SELECT query in a sender jdbc comm channel

[Former Member]

Hi experts,

I would like to access a table in a database and select some rows from there but from a specific date onwards. I mean, i want to retrieve only the rows from, lets say, table.date = 2008-04-05 on.

So i specified somthing like:

*SELECT * FROM table WHERE date >= to_date(‘2008-04-05’) and status ='0'*

UPDATE table SET status='1' WHERE date >= to_date(‘2008-04-05’) and status ='0'

BUT this is throwing following error message:

The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-00911: invalid character '. For details, contact your database server vendor.

Could you please provide some support in order to achieve this?

Thanks in advance and best regards,

David

[Former Member]

Check also, if the column status is of tyep Character. Is it? Or is it Integer? Then ' ' is wrong...

Peter

[Former Member]

Hi there,

I tried again without using the 'to_date' function but still doesnt work.

I have no access to the database so i asked the DB team to make some testing there. They say the date format in the DB is 'dd-mm-YYYY' but XI receives 'YYYY-mm-dd' via jdbc adapter!!!!

So, at first i typed 'YYYY-mm-dd' in the query and this was the result:

Database-level error reported by JDBC driver while executing statement 'SELECT * FROM albaranes,lineas WHERE albaranes.albaran = lineas.albaran and albaranes.fecha_albaran >= ‘2004-06-19’ and albaranes.sap_status ='3''. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-00911: invalid character '. For details, contact your database server vendor.

and shortly afterwards i used 'dd-mm-YYYY' and this is what i got:

Database-level error reported by JDBC driver while executing statement 'SELECT * FROM albaranes,lineas WHERE albaranes.albaran = lineas.albaran and albaranes.fecha_albaran >= '19-06-2004' and albaranes.sap_status ='3''. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-01843: not a valid month '. For details, contact your database server vendor.

sap_status field is of string type so this is not the point, i think.

Could you please advice?

Best regards,

David

Edited by: Domingo Jimenez Navarrete on May 5, 2008 12:18 PM

[Former Member]

try this instead :

SELECT * FROM albaranes,lineas WHERE albaranes.albaran = lineas.albaran and albaranes.fecha_albaran >= CONVERT(datetime,'2004-06-19',120) and albaranes.sap_status ='3'

It assumes fecha_albaran is a datetime mssql column, therefore the convert function will "cast" your string value 2004-06-19 to a datetime format with the suitable format (http://msdn.microsoft.com/en-us/library/ms187928.aspx)

Let me know if it works ...

Chris

PS : the "style" option (here 102) is not mandatory, it seems to work without providing it

Edited by: Christophe PFERTZEL on May 5, 2008 12:22 PM

Edited by: Christophe PFERTZEL on May 5, 2008 12:25 PM

[Former Member]

Hi,

Still does not work, i tested and this is what i get:

ORA-00904: "DATETIME": invalid identifier '.

Any other clue?

Best regards,

david

[Former Member]

my mistake, for some unknown reasons, I thought you were running MSSQL ! Shame on me ...

Ok, so now let's go back to ORACLE tech : what is the column type for your date value ? If it uses a DATE type, then try this :

SELECT ... WHERE albaranes.fecha_albaran >= to_char(to_date('2008-04-05’,'yyyy-mm-dd'),'dd-mm-yyyy') ...

It is a 2 step date formatting :

1 - your string value containing a litteral representation of your date is converted to a date object according to its current format

2 - this date object is then transformed into a new litteral date representation with the expected target pattern format, which can be internally casted to ORACLE DATE type

I've tried it in my SQL client and it does not raise any error

Edited by: Christophe PFERTZEL on May 5, 2008 2:28 PM

[Former Member]

Hi Christophe,

Yes, in my database, the date type is DATE. But i cannot understand why are you converting it to char, so far...

If i use the query you told me, i receive a 'missing right parenthesis' ORA error message. However, i doublecheck the statement and do not see where i mispelled sthg.

I only want to select some rows depending on the date field they have in the db record. These fields are DATE datatypes.

can you please give any other suggestion?

Thanks in advance and best regards,

david

[Former Member]

Hi Domingo,

I'm not sure why are you using the date function 'to date' in the select query. Even if you directly try to put the date value, it will work. Just put the date in the format expected by the database.

Regards,

Akshay

[Former Member]

Can you change the statement using the following format:

to_date( string1, [ format_mask ], [ nls_language ] )

where format_mask and nls_language are optional.

still try using:

to_date('2008-04-05', 'YYYY-MM-DD')

Regards,

Indranil

Award points if helpful

[Former Member]

Indrani,

I checked whay you proposed but..........same error!!

I am using ojdbc14 driver.

Any other clue?

Best regards,

david

[Former Member]

Hi,

this is not a problem of the driver, but the query.. Try to copy the query exactly as it is in the adapter configuration and try it in DB directly usinf some QueryAnalyzer or some tool... I can't remember now , what tool I used for queries to Oracle.. (FROG? ) I don't know..

Anyways, try the queries from the other tool...

btw: why do you have asterisk at the beginning of the select (and end as well)?

Peter