JDBC Adapter: SELECT statement with substring

[Former Member]

Hi,

I use the JDBC Sender Adapter for getting data out of an oracle database and I my select out of an nonSAP system looks like this:

database:

hkey hdatajhz hdatam hdatat

1300234 2005 05 01

select XXXXX from <database>

where

hdatajhz||hdatam||hdatat = '20050501'

and substr(hkey,3,2) = '00'

Both statements doesent't work, why??

Regards

Christoph

[Former Member]

did you test your SQL query with your database ?

I am not a Pro but i think the you cannot use <b>susbtr</b> in the where condition. I guess you need to use <b>LIKE</b> statement.

One other thing is <b>hdatajhz||hdatam||hdatat</b> it one field or you wanted to use as <b>OR</b> ? The SQL Query does not look right to me.

<b>

>> select XXXXX from <database>

>> where

>> hdatajhz||hdatam||hdatat = '20050501'

>> and substr(hkey,3,2) = '00'</b>

I think you should use table name instead of <b><database></b>

check the query using your SQL Plus or whatever the tool you are using to access the database.

regards

SKM

[Former Member]

where can i find documentation/samples for sql statements in XI?

the date is splitt in 3 fields in this table.

hdatajhz 2005

hdatam 04

hdatat 23

is it possible to concate tabel-fields in the sql string?

hdatajhz||hdatam||hdatat = '20050423'

regads

christoph