It looks like the reason for the original rewrite was due to subquerying
the same table twice. Nothing wrong with that, but the wizard decides to
ensure that every reference is unique, thus the aliasing of the second
reference. You could get around that by adding your own table aliases in
th
> I would recommended creating the view from a query window,
> and not the
> design view that SQL Management studio uses
+1 . Traditionally design views and wizards do unspeakable things to your
nicely formatted sql.
-Leigh
AM
To: cf-talk
Subject: Re: (ot) SQL query aliases in SQL Management Studio
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it
Hi Jason,
I would recommended creating the view from a query window, and not the
design view that SQL Management studio uses
Simply add a 'Create View dbo. AS' before the statement that works and
run it in the query window
Hope this helps
-Dave
On Tue, Dec 22, 2009 at 11:34 AM, Jason Durha
My apologies for posting an OT message to a ColdFusion list. RDMS are so
closely tied to our day-to-day job in writing CF, that perhaps the list will be
tolerant of such a question.
I'm writing a View for our CRM package to use. The SQL works as I've written
it, but Management Studio keeps a
y easier way to do this.
> Thanks
> -- Jeff
>
> From: S. Isaac Dealey <[EMAIL PROTECTED]>
> Sent: Friday, May 20, 2005 9:16 AM
> To: CF-Talk
> Subject: Re: OT: SQL Query Question
> select t.* from test t where t.actid in
&g
I knew there was any easier way to do this.
Thanks
-- Jeff
From: S. Isaac Dealey <[EMAIL PROTECTED]>
Sent: Friday, May 20, 2005 9:16 AM
To: CF-Talk
Subject: Re: OT: SQL Query Question
select t.* from test t where t.actid in
(SELECT MAX(t2.actID
Jeff Chastain wrote:
>
> I have a table that looks something like this
>
> crIDactIDdescriptionowner
> 1 1Test 444
> 1 2Test Update124
> 2 1Test 578
>
> Now, what I need is a listing of all d
select t.* from test t where t.actid in
(SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID)
ought to work...
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusio
Sorry for the off-topic, but I have been beating on this one for a while and
the only answer I can find is really, really ugly. So, hopefully somebody
knows more about queries that I do and can show me the error of my ways
I have a table that looks something like this
crIDact
Ian Skinner wrote:
> I remember a thread recently where DBMS execution plans where used to debug a
> problematic query. I now have a need to explore this on an Oracle 8/9 system
> for documentation and debugging. I'm off to Google to see what I can find
> out on how one does this with Oracle s
I remember a thread recently where DBMS execution plans where used to debug a
problematic query. I now have a need to explore this on an Oracle 8/9 system
for documentation and debugging. I'm off to Google to see what I can find out
on how one does this with Oracle systems. But I though I wou
Shouldn't that be a left join, since you always want to include the
levels information?
Adam H
On Fri, 13 Aug 2004 16:50:25 -0500, Paul Giesenhagen
<[EMAIL PROTECTED]> wrote:
> I tried to ask this on Experts Exchange without any luck ... maybe one of you guys may be able to help out.
>
> I am tr
I tried to ask this on Experts Exchange without any luck ... maybe one of you guys may be able to help out.
I am trying to build some pricing levels into our application and having a hard time with SQL query, here is an explination.
I have 2 tables:
LEVELS
levelID, code, descrip
I have three tables and I know the guideID. The Fusion table below brings the guides and the lakes together. If a Guide is a guide at 1 or more lakes, the lakeID and the guideID are put into a row into the fusion table. Guides can be associated with many lakes and many lakes associated with a gu
Talk
Subject: Re: OT: SQL Query
Ryan Roskilly said:
>
>
> SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
> tbl_event.Event_Date, tbl_event.Event_Title,
> tbl_location.location_Name
> FROM tbl_event, tbl_location
Ryan Roskilly said:
>
>
> SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
> tbl_event.Event_Date, tbl_event.Event_Title,
> tbl_location.location_Name
> FROM tbl_event, tbl_location
> WHERE 1 = 1
> AND (tbl_event.Event_LocationID = tbl_location.location_ID
>
I have the following query
SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
tbl_event.Event_Date, tbl_event.Event_Title,
tbl_location.location_Name
FROM tbl_event, tbl_location
WHERE 1 = 1
AND (tbl_event.Event_LocationID = tbl_location.location_ID
OR
Just thinking out loud ...
Is there a reason you're not passing the tripDate
of the report you are viewing as the variable, rather
than the reportID (assuming that since you are able
to retrieve the ID, you are also able to retrieve
the date ... )
The query to get the previous/next could then
loo
pDate DESC
UPDATE beaverReports
SET tripSort = #thisQuery.currentRow#
WHERE reportID = #thisQuery.reportID#
Paul Giesenhagen
QuillDesign
- Original Message -
From: Tony Weeg
To: CF-Talk
Sent: Friday, October 17, 2003 12:01 PM
Subject: RE: OT: SQL Query
does
TECTED]
410.548.2337
-Original Message-
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 12:40 PM
To: CF-Talk
Subject: Re: OT: SQL Query
Ok here is the query that I have ... and the error that is showing up. I am
using MS SQL 2000
(
SELECT TOP 1 reportI
pDate < (SELECT tripDate FROM beaverReports WHERE reportID = 1032) ORDER BY tripDate"
Thanks for the help!
Paul Giesenhagen
QuillDesign
- Original Message -
From: Jochem van Dieten
To: CF-Talk
Sent: Friday, October 17, 2003 3:56 AM
Subject: Re: OT: SQL Query
Paul Gi
m van Dieten
To: CF-Talk
Sent: Friday, October 17, 2003 3:56 AM
Subject: Re: OT: SQL Query
Paul Giesenhagen said:
>
> I have a number of reports all dated, but dated by day (no time).
> Some of the reports are on the same day If I have an ID value, I
> need to grab the
Paul Giesenhagen said:
>
> I have a number of reports all dated, but dated by day (no time).
> Some of the reports are on the same day If I have an ID value, I
> need to grab the report before and the report after that ID and it
> needs to be by date.
>
> Here is what the data looks like:
>
> Repor
I need to write a query and really don't know where to start -- here is the description.
I have a number of reports all dated, but dated by day (no time).
Some of the reports are on the same day If I have an ID value, I need to grab the report before and the report after that ID and it needs to be
Jeff Chastain wrote:
>
> Table 1 (key,name) -
> 1,test
> 2,test2
>
> Table 2 (key,thread,timestamp) -
> 1,1,1/1/2003
> 1,2,1/2/2003
> 2,3,1/1/2003
> 1,2,1/4/2003
>
> The desired result set, passing in a key = 1 parameter, would be
>
> key,name,numthreads,timestamp
> 1,test,2,1/4/2004
>
> The
Nice SQL Jochem...Also to clarify, Jeff, since you said that you need to get all the
fields from table one: The GROUP BY statement needs to include *all* of your
non-aggregate-function fields, in a comma-delimited list. So if you're selecting
t1.key, t1.name, table1.alias in addition to the max
Jochem,
Either I am not following your code or I may not have made myself clear. Take the
following example ...
Table 1 (key,name) -
1,test
2,test2
Table 2 (key,thread,timestamp) -
1,1,1/1/2003
1,2,1/2/2003
2,3,1/1/2003
1,2,1/4/2003
The desired result set, passing in a key = 1 parameter, wou
Jeff Chastain wrote:
>
> I have two tables with a one to many relationship. I need to pull all the
> records from table one with one field in the resulting recordset being a
> count of how many corresponding records are in the second table and another
> field being the date of the latest corresp
I have written way to much code today and this is not coming to me as fast
as it should.
I have two tables with a one to many relationship. I need to pull all the
records from table one with one field in the resulting recordset being a
count of how many corresponding records are in the second ta
Hi All,
It's late and this escapes me. I've done it before, but my mind is drawing
a blank.
I have a database table that contains a field with bitflags. All flags off
is 0, all flags on is 255, you get the idea.
What I'm trying to do is query the database table and have it show me all
records
A huge thanks to all who took the time to respond. Now I am a little wiser
and it is working.
Tony Gruen
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_list
ah yeah, you got the picture ;-) The key is that you need to include the
timestamp info.
-Original Message-
From: Scott Brady [mailto:[EMAIL PROTECTED]
Sent: 01 July 2003 17:07
To: CF-Talk
Subject: RE: Little OT: SQL Query Date Filter
-- Original Message
-- Original Message --
From: "Robertson-Ravo, Neil (RX)" ermhow about :
>
>WHERE bDate >= #variables.EDate# 00:00:00
>AND bDate <= #variables.TDate# 23:59:59
Since that would get 3 days' worth (that would get you transactions on EDate and
TDate, wouldn
es.EDate#
and bDate < #Variables.CDate#
but set EDate = today not yesterday.
Steve
-Original Message-
From: Tony Gruen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 11:56 AM
To: CF-Talk
Subject: Little OT: SQL Query Date Filter
I have been testing this and cannot envision
e 410.548.2337
fax 410.860.2337
-Original Message-
From: Tony Gruen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2003 9:56 AM
To: CF-Talk
Subject: Little OT: SQL Query Date Filter
I have been testing this and cannot envision a solution. SQL Server 2000
and CF5.
I have a small table that rec
ermhow about :
WHERE bDate >= #variables.EDate# 00:00:00
AND bDate <= #variables.TDate# 23:59:59
SQL Server uses the timestamp alongside the date as well.
-Original Message-
From: Tony Gruen [mailto:[EMAIL PROTECTED]
Sent: 01 July 2003 16:56
To: CF-Talk
Subject: Little O
I have been testing this and cannot envision a solution. SQL Server 2000 and
CF5.
I have a small table that records transactions. The date for each
transaction is recorded in createodbcdatetime format. Transactions can occur
throughout the day.
I am creating a page that shows the days transaction
Using SQL Server, how would I define a query to return ranges of ID's
(primary key) where a tinyint field was not true?
I'm looking for any ranges (eg; 12202 - 12249 and 14477 - 15225) where that
tinyint field is 0. I'd like to specify a minimum number of records in that
range, but it's not absol
]>
Sent: Monday, September 16, 2002 11:28 PM
Subject: RE: OT SQL query (dates)
> How about...
>
>
> SELECT sd.id
> FROM settingTable AS sd,
> settingTable AS ed
> WHERE (sd.datatype="startdate")
> AND (ed.datatype="enddate")
> AND (
> (sd
SELECT c.id, c.description
FROM choiceTable c
WHERE c.id = '#url.productid#'
AND NOT EXISTS (
SELECT pc.id
FROM ProductChoiceTable pc
WHERE pc.productid='#url.productid#'
)
--- Paul Giesenhagen <[EMAIL PROTECTED]> wrote:
> I have a query that I am trying to get all my
> descriptions from my
I have a query that I am trying to get all my descriptions from my choiceTable that
are not found in my productChoiceTable that have a productid of a url.product value.
Basically if a choice id is found in the productChoiceTable that also has the
url.productid in the productid column, it is di
Yes, that is where the problem arises...
Basically if inventory is > 0 show the item... no problems, but if the
inventory level is <= 0 I need to see if backordering is allowed and if it
is, show the item, if it isn't do not show the item.
Make sense?
Paul Giesenhagen
QuillDesign
> First thi
First thing I see, Paul is that you say
"if inventory is 0 or below, then I need to check backorder value"
But your SQL says:
... and inventory > 0 ...
???
Aren't these opposites?
On 5/20/02 8:38 PM, "Paul Giesenhagen" <[EMAIL PROTECTED]> wrote:
> I am writing a join query that displays
I am writing a join query that displays information depending on a value,
There are two columns that deal with inventory, they are
inventory = number in stock (could be a negative)
backorder = either a 1/0 do we allow backordering or not, if it is 1 Yes, we allow
backordering if 0 No we do not.
what you're after but, hopefully, it will point you in
the right direction.
Enjoy your weekend ;)
Dina
- Original Message -
From: Paul Giesenhagen
To: CF-Talk
Sent: Friday, April 12, 2002 10:51 PM
Subject: OT SQL Query
Ok, I have an operation that I need help writ
quot; <[EMAIL PROTECTED]>
Sent: Friday, April 12, 2002 8:51 PM
Subject: OT SQL Query
> Ok, I have an operation that I need help writing a query for... it may be
simple or it may be impossible... I will do my best to explain what I am
wanting in advance, thanks for your help.
>
>
Ok, I have an operation that I need help writing a query for... it may be simple or it
may be impossible... I will do my best to explain what I am wanting in advance,
thanks for your help.
I have a table: (owner and sub_object values are ID values in another table)
idownersub_
I'm using Aliases
SELECT A.ResortName, B.Year, B.News
FROM Resorts A, NewFor B
WHERE B.ResortID = A.ResortID
ORDER BY B.Year
hehehehe
>From: "Jim McAtee" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: OT: SQ
I could use a bit of help with what I'm guessing is a fairly easy query.
I've got the following:
Tables:
Resorts
NewFor
Resorts Table:
ResortID pk
ResortName
NewFor Table:
NewForID pk
ResortID fk
Year
News
In the NewFor table I've got news for 1999, 2000, 2001, etc. for most (but
not all resor
I don't know if any of you know how to do it but I'm trying to get the SQL
query pasted below to include the two dates, at the moment it doesn't return
data from the two dates specified. ie It returns queries starting Jan 02 and
ending 19 Feb
:-(
SELECT DateAdded, EmailA
FROM
51 matches
Mail list logo
