topic Re: EXEC SQL: SELECT ERROR in Application Development and Automation Discussions

EXEC SQL: SELECT ERROR

Former Member — Mon, 24 Jan 2011 17:01:22 GMT

Hi,

can you pls tell me, why the first EXEC SQL doesn't work (An SQL error occurred when executing Native SQL. The error 1460 occurred in the current database connection "CON1") while the second works fine? I'm connecting to Oracle DB where CREATETIME is type DATE (for example 26.10.06 22:31:38)

EXEC SQL.

OPEN dbcur1 FOR

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME >= to_date(:w_date_from, 'ddmmyyyy')

and CREATETIME < to_date(:w_date_to, 'ddmmyyyy')

ENDEXEC.

EXEC SQL.

OPEN dbcur1 FOR

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME >= to_date(:w_date_from, 'ddmmyyyy')

ENDEXEC.

Re: EXEC SQL: SELECT ERROR

Former Member — Mon, 24 Jan 2011 20:04:16 GMT

and CREATETIME >= to_date(:w_date_from, 'ddmmyyyy')
and CREATETIME < to_date(:w_date_to, 'ddmmyyyy')

would not to_date contain the same value? If so, createtime could not possibly met the where clause statement above...should you have used

and CREATETIME >= from_date(:w_date_from, 'ddmmyyyy')
and CREATETIME < to_date(:w_date_to, 'ddmmyyyy')

Re: EXEC SQL: SELECT ERROR

Clemenss — Mon, 24 Jan 2011 21:48:23 GMT

Hi mado,

this may depend on the database you are reading from. I.e. [SQL Server Date Formats|http://www.sql-server-helper.com/tips/date-formats.aspx] may not match here.

Also, w_date_to is not used in second SQL statement.

Regards,

Clemens

Edited by: Clemens Li on Jan 24, 2011 10:50 PM

Re: EXEC SQL: SELECT ERROR

Former Member — Tue, 25 Jan 2011 15:37:49 GMT

They're not the same. One parameter is w_date_from and other is w_date_to. Both are formatted by Oracle function to_date. Statement is working fine only with one condition for the date. For example :

EXEC SQL.

OPEN dbcur1 FOR

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME >= to_date(:w_date_from, 'ddmmyyyy')

ENDEXEC.

It's not working with two conditions for the date selection:

EXEC SQL.

OPEN dbcur1 FOR

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME >= to_date(:w_date_from, 'ddmmyyyy')

and CREATETIME < to_date(:w_date_to, 'ddmmyyyy')

ENDEXEC.

Why???

Re: EXEC SQL: SELECT ERROR

Clemenss — Tue, 25 Jan 2011 16:28:28 GMT

Hi mado,

what happens if you use only :w_date_to ?

BTW: Whats the databse used?

Regards,

Clemens

Re: EXEC SQL: SELECT ERROR

Former Member — Wed, 26 Jan 2011 14:22:49 GMT

It works fine ...

EXEC SQL.

OPEN dbcur1 FOR

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME < to_date(:w_date_to, 'ddmmyyyy')

ENDEXEC.

Re: EXEC SQL: SELECT ERROR

Former Member — Wed, 26 Jan 2011 14:35:50 GMT

Also full statement in DB (Oracle 9.2.0.1.0) console works with no errors:

select DOCUMENTCODE, CANCELDOCID, DISPATCHERNAME, TERMINALADDRESS

from TAMAS.deliveryinvoice

where CANCELDOCID IS NOT NULL

and CREATETIME >= to_date(19012011, 'ddmmyyyy')

and CREATETIME < to_date(20012011, 'ddmmyyyy')