Shot in the dark, because one of the columns you've put in really might not
exist, but try getting rid of createodbcdate and use the to_date() function
in Oracle, as it is very particular about date formatting.
For example, instead of:
AND (STSERVICE.DATE_BEGIN BETWEEN #createodbcdate('07/01/00')# AND
#createodbcdate('06/30/01')#
try:
AND (STSERVICE.DATE_BEGIN BETWEEN to_date('07/01/00', 'MM/DD/YYYY') AND
to_date('06/30/01', 'MM/DD/YYYY')
If the dates are dynamic (which I'm sure they are), just replace the
'07/01/00' with the CF variables name. Try staying away from
CreateODBCDate().
HTH,
Dave.
----- Original Message -----
From: "Kris Pilles" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, October 15, 2002 03:53 PM
Subject: Oracle query help
> Heres the query:
>
> SELECT ALL STUDENT.STUDENT_NUM,
> STUDENT.LASTNAME, STUDENT.FIRSTNAME, STUDENT.BIRTHDATE, STUDENT.SEX,
> STUDENT.SSNUM,
> STSERVICE.DATE_BEGIN, STSERVICE.DATE_END, STSERVICE.LOCATION,
> STSERVICE.HANDICAP,
> STSERVICE.CSIZE_RATIO, STSERVICE.ST_CLASS, STSERVICE.FUND,
> STSERVICE.SERVICE, CUST1.CODE, CUST2.CODE, ST_CONTACTS.ID,
> ST_ADDRESSES.ADDRESS1_TX, ST_ADDRESSES.ADDRESS2_TX,
> ST_ADDRESSES.CITY_TX, ST_ADDRESSES.STATE_CD, ST_ADDRESSES.ZIP_NR
> FROM STUDENT, STSERVICE, ST_CONVERT_CUSTOMER CUST1, ST_CONVERT_CUSTOMER
> CUST2, ST_CONTACTS, ST_ADDRESSES
> WHERE ((STSERVICE.STUDENT_NUM = STUDENT.STUDENT_NUM)
> AND (ST_CONTACTS.STUDENT_NUM(+) = STUDENT.STUDENT_NUM)
> AND (ST_ADDRESSES.ST_CONTACT_ID(+) = ST_CONTACTS.ID)
> AND (STSERVICE.CUSTOMER = CUST1.CUSTOMER(+))
> AND (STSERVICE.HOME_DISTRICT = CUST2.CUSTOMER(+))
> AND (STSERVICE.DATE_BEGIN BETWEEN #createodbcdate('07/01/00')# AND
> #createodbcdate('06/30/01')#
> OR STSERVICE.DATE_END BETWEEN #createodbcdate('07/01/00')# AND
> #createodbcdate('06/30/01')#
> OR XSTART BETWEEN STSERVICE.DATE_BEGIN AND STSERVICE.DATE_END))
> </cfquery>
>
> The error:
>
> **All column/table names are correct
>
> [Oracle][ODBC][Ora]ORA-00904: invalid column name
>
> Any ideas??? Any help would be appreicated.
>
> THanks
>
>
>
> Kris Pilles
> Website Manager
> Western Suffolk BOCES
> 507 Deer Park Rd., Building C
> Phone: 631-549-4900 x 267
> E-mail: [EMAIL PROTECTED]
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
