Re: OT - Complex SQL
Jillian,
If you're using DB2 UDB, you can use the date() function to pass in your
date:
where table_1.expiry_date = date('2005-12-31') ...
Dina
On 10/28/05, Dawson, Michael [EMAIL PROTECTED] wrote:
Along these lines, you might want to look into DB2's data dictionary.
On our AS400, you can find the data dictionary in the qsys2 libraries.
Look for a table named syscolumns. That table will give you the
details on any column in the database.
I created a simple DB2 datadictionary view a few years ago and it comes
in quite handy.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:41 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You're so right... Anybody happen to now offhand (I'm off to search
after I send this message) what the date format is for DB2?
I'm just running this at the command line, so no need for any CF
stuffs... But unfortuantely also can't take advantage of CF stuffs.
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 4:16 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You need single quotes around each of your values in the IN clause.
snip
AND table_1.table_id IN ('value1','value2','value3') /snip
The date formats may be wrong, too. Check the actual data in the
database and/or use createODBCDateTime() and definitely use
CFQUERYPARAM. It will make your job a bit eaiser.
Either way, I would remove the dates from your WHERE claus, fix the IN
clause, then run the query. See if you get any results with very
limited filtering, first.
Then, go from there and add more filtering as needed.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:12 PM
To: CF-Talk
Subject: OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROM table_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222645
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROMtable_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222623
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Complex SQL
You need single quotes around each of your values in the IN clause.
snip
AND table_1.table_id IN ('value1','value2','value3')
/snip
The date formats may be wrong, too. Check the actual data in the
database and/or use createODBCDateTime() and definitely use
CFQUERYPARAM. It will make your job a bit eaiser.
Either way, I would remove the dates from your WHERE claus, fix the IN
clause, then run the query. See if you get any results with very
limited filtering, first.
Then, go from there and add more filtering as needed.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:12 PM
To: CF-Talk
Subject: OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROMtable_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222625
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Complex SQL
I don't know what DBMS you are using, but should you have quotes around
all of the values in your IN clause, like so:
AND table_1.table_id IN ('value1','value2','value3')
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 4:12 PM
To: CF-Talk
Subject: OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECTtable_1.expiry_date,
table_3.expiry_date
FROM table_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
[INFO] -- Access Manager:
This transmission may contain information that is privileged, confidential
and/or exempt from disclosure under applicable law. If you are not the
intended recipient, you are hereby notified that any disclosure, copying,
distribution, or use of the information contained herein (including any
reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in
error, please immediately contact the sender and destroy the material in its
entirety, whether in electronic or hard copy format. Thank you. A2
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222626
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT - Complex SQL
AND table_1.table_id IN ('value1','value2','value3')
try that
On 10/28/05, Jillian Koskie [EMAIL PROTECTED] wrote:
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROMtable_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222627
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Complex SQL
You're so right... Anybody happen to now offhand (I'm off to search
after I send this message) what the date format is for DB2?
I'm just running this at the command line, so no need for any CF
stuffs... But unfortuantely also can't take advantage of CF stuffs.
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 4:16 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You need single quotes around each of your values in the IN clause.
snip
AND table_1.table_id IN ('value1','value2','value3')
/snip
The date formats may be wrong, too. Check the actual data in the
database and/or use createODBCDateTime() and definitely use
CFQUERYPARAM. It will make your job a bit eaiser.
Either way, I would remove the dates from your WHERE claus, fix the IN
clause, then run the query. See if you get any results with very
limited filtering, first.
Then, go from there and add more filtering as needed.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:12 PM
To: CF-Talk
Subject: OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROMtable_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222628
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Complex SQL
You might try SELECTing the date column and see what it returns.
You might also ask the DBA what the date datatype is.
I know, from sad experience, that DB2 DBA's tended to use non-date
datatypes because they are somewhat new in the last decade.
We have DB2 on our AS400 and NO ONE uses a date/time datatype. Instead,
we have a mix of char- and numeric-based datatypes.
For example, we may have dates stored as: 20051031 or '20051031'.
What you want is to see an output like: {2005-10-31 15:31:59.000}
Or something similar to that. If you do see that format, then you have
a good date/time datatype. These date/time values are pretty standard
and most languages can parse them correctly.
If you have a canonical format like 20051031, then you will need to
manually parse it before using any of CF's date/time functions, if
desired.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:41 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You're so right... Anybody happen to now offhand (I'm off to search
after I send this message) what the date format is for DB2?
I'm just running this at the command line, so no need for any CF
stuffs... But unfortuantely also can't take advantage of CF stuffs.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222636
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: OT - Complex SQL
Along these lines, you might want to look into DB2's data dictionary.
On our AS400, you can find the data dictionary in the qsys2 libraries.
Look for a table named syscolumns. That table will give you the
details on any column in the database.
I created a simple DB2 datadictionary view a few years ago and it comes
in quite handy.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:41 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You're so right... Anybody happen to now offhand (I'm off to search
after I send this message) what the date format is for DB2?
I'm just running this at the command line, so no need for any CF
stuffs... But unfortuantely also can't take advantage of CF stuffs.
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 4:16 PM
To: CF-Talk
Subject: RE: OT - Complex SQL
You need single quotes around each of your values in the IN clause.
snip
AND table_1.table_id IN ('value1','value2','value3') /snip
The date formats may be wrong, too. Check the actual data in the
database and/or use createODBCDateTime() and definitely use
CFQUERYPARAM. It will make your job a bit eaiser.
Either way, I would remove the dates from your WHERE claus, fix the IN
clause, then run the query. See if you get any results with very
limited filtering, first.
Then, go from there and add more filtering as needed.
M!ke
-Original Message-
From: Jillian Koskie [mailto:[EMAIL PROTECTED]
Sent: Friday, October 28, 2005 5:12 PM
To: CF-Talk
Subject: OT - Complex SQL
Okay, I think it's because it is Friday at 4... can somebody help me
out?
SELECT table_1.expiry_date,
table_3.expiry_date
FROMtable_3
JOIN table_2 ON table_2.table2_key = table_3.tablel2_key
JOIN table_1 ON table_1.table1_key = table_2.table1_key
WHERE table_1.expiry_date = 'Dec 31, 2005'
AND table_3.expiry_date = 'Dec 31, 2005'
AND table_1.table_id IN ('value1,value2,value3')
This isn't returning results, and it should... where is my syntax
incorrect?
--
Jillian
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222637
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
