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
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
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 in the query window
Hope
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
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
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
crID
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
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 distinct
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 cf-talk@houseoffusion.com
Subject: Re: OT: SQL Query Question
select t.* from test t where t.actid
this.
Thanks
-- Jeff
From: S. Isaac Dealey [EMAIL PROTECTED]
Sent: Friday, May 20, 2005 9:16 AM
To: CF-Talk cf-talk@houseoffusion.com
Subject: Re: OT: SQL Query Question
select t.* from test t where t.actid in
(SELECT MAX(t2.actID) FROM test t2
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
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
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 trying
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,
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 guide.
I have the following query
cfquery name=qryEvent datasource=#request.ds#
SELECTtbl_event.Event_ID, tbl_event.Event_LocationID,
tbl_event.Event_Date, tbl_event.Event_Title,
tbl_location.location_Name
FROM tbl_event, tbl_location
WHERE1 = 1
AND (tbl_event.Event_LocationID =
Ryan Roskilly said:
cfquery name=qryEvent datasource=#request.ds#
SELECTtbl_event.Event_ID, tbl_event.Event_LocationID,
tbl_event.Event_Date, tbl_event.Event_Title,
tbl_location.location_Name
FROM tbl_event, tbl_location
WHERE1 = 1
AND (tbl_event.Event_LocationID = tbl_location.location_ID
ORtbl_event.Event_LocationID = 1000
) 4
ORDER BY tbl_event.Event_Date ASC
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23, 2003 10:05 AM
To: CF-Talk
Subject: Re: OT: SQL Query
Ryan Roskilly said:
cfquery name=qryEvent datasource=#request.ds
cfloop query=thisQuery
UPDATE beaverReports
SET tripSort = #thisQuery.currentRow#
WHERE reportID = #thisQuery.reportID#
/cfloop
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
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
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:
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 report before and the report after that ID
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 Giesenhagen said:
I have a number of reports all dated
]
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 reportID
FROM
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
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 difference I am
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
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
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,
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
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
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
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 OT: SQL
-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 records transactions. The date
#
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 a solution. SQL
-- Original Message --
From: Robertson-Ravo, Neil (RX) Neil.Robertson-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
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
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:
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
: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.datedata Between #form.startdate#
And #form.enddate#)
OR (ed.datedata Between #form.startdate
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
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 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.
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
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 thing
, 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.
I have a table: (owner and sub_object values are ID values
: 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 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
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
48 matches
Mail list logo
