Try an easier way to create and test SQL:
lc_custno = [123]
lc_date = DTOT(DATE(2004, 7, 1))
TEXT to lc_SQL TEXTMERGE NOSHOW PRETEXT 3
SELECT *
FROM MyOracle.Sales Sales
WHERE Sales.customer = ?lc_custno
AND Sales.OrderDate >= ?lc_date
ORDER BY Sales.OrderDate
ENDTEXT
lc_SQL = STRTRAN(lc_SQL, CHR(13) + CHR(10), [ ])
nresult = SQLEXEC(myhandle, lc_SQL, 'myresults')
>I am trying to query an Oracle table. The following works fine except when I try to add a where clause for the OrderDate field.
>
>What would be the proper way to query this Oracle table with a where clause on the OrderDate (datetime) field?
>
>***This works without the OrderDate clause.
>lc_custno = [123]
>lc_sql = [SELECT * ] + ;
> [FROM MyOracle.Sales Sales ] + ;
> [WHERE Sales.customer = '] + lc_custno + [' ] + ;
> [ORDER BY Sales.OrderDate]
>nresult = SQLEXEC(myhandle, lc_sql, 'myresults')
>
>***This does not work and the nresult = -1.
>lc_custno = [123]
>lc_date = [07/01/2004 12:00:00 AM]
>lc_sql = [SELECT * ] + ;
> [FROM MyOracle.Sales Sales ] + ;
> [WHERE Sales.customer = '] + lc_custno + [' ] + ;
> [AND Sales.OrderDate >= '] + lc_date + [' ] + ;
> [ORDER BY Sales.OrderDate]
>nresult = SQLEXEC(myhandle, lc_sql, 'myresults')
>
>How can I filter a datetime field in a Oracle SQL call?
>
>Thanks,
>
>Jerry
Mark McCasland
Midlothian, TX USA