EXEC SQL: SELECT ERROR

[Former Member]

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.

[Former Member]

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')

[Former Member]

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???

[Clemenss]

Hi mado,

what happens if you use only :w_date_to ?

BTW: Whats the databse used?

Regards,

Clemens

[Former Member]

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.

[Former Member]

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')

[Clemenss]

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