MQSeries.net :: View topic - Oracle where clause with Date function

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle where clause with Date function

Oracle where clause with Date function

« View previous topic :: View next topic »

Author

Message

cvanmook

Posted: Mon Jan 31, 2005 7:44 am Post subject: Oracle where clause with Date function

Novice

Joined: 16 Jan 2004
Posts: 23

Hello,

We have a problem selecting a row from an Oracle table which has in it's where clause a date field.

The select clause is as follows:

SET r_Env.check[] = SELECT T.NUM_MEDEWERKER FROM Database.MABZHM.TEAM_MEDEWERKER AS T WHERE T.NUM_MEDEWERKER = v_m_NUM_MEDEWERKER AND T.DATE= v_m_DATE

The variable v_m_DATE is in esql format DATE
The T.DATE is an Oracle Date field in de database.

Must we do something extra for this to make this work. The strange thing
is that an insert or update is working wel without additional work.

Thanx in advance,

Christan

Posted: Mon Jan 31, 2005 9:48 am Post subject:

Padawan

Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.

What's the problem? No match on the database? Is a SQL error generated?

cvanmook

Posted: Mon Jan 31, 2005 9:55 am Post subject:

Novice

Joined: 16 Jan 2004
Posts: 23

Jan 31 14:20:49 r2l03 MQSIv500[57128]: (WBRK01.MARTENSO)[5141]BIP2519E: (, 1.1)
: Error executing SQL statement 'SELECT T.NUM_MEDEWERKER FROM MABZHM.CONTRACT T
WHERE ((T.NUM_MEDEWERKER)=(?))AND((T.DAT_INGANG)=(?))' against datasource 'MABZH
M' with parameters '76293, DATE '2004-02-23', '. : WBRK01.15f89696-ff00-0000-0
080-8d9b028fcfae: /build/S500_P/src/DataFlowEngine/ImbRdl/ImbRdlExternalDb.cpp:
277: SqlExternalDbStmt::executeStmt: ComIbmDatabaseNode: MAB001_GPM#FCMComposite
_1_3
Jan 31 14:20:49 r2l03 MQSIv500[57128]: (WBRK01.MARTENSO)[5141]BIP2321E: Database
error: ODBC return code '-1'. : WBRK01.15f89696-ff00-0000-0080-8d9b028fcfae: /b
uild/S500_P/src/DataFlowEngine/ImbOdbc.cpp: 185: ImbOdbcHandle::checkRcInner: :
Jan 31 14:20:49 r2l03 MQSIv500[57128]: (WBRK01.MARTENSO)[5141]BIP2322E: Database
error: SQL State 'HY000'; Native Error Code '932'; Error Text '[DataDirect][ODB
C Oracle driver][Oracle]ORA-00932: inconsistent datatypes Error in parameter 2
.'. : WBRK01.15f89696-ff00-0000-0080-8d9b028fcfae: /build/S500_P/src/DataFlowEng
ine/ImbOdbc.cpp: 327: ImbOdbcHandle::checkRcInner: :

This is the error we've got ... it looks like the date must be casted but how to do ...?

cvanmook

Posted: Mon Jan 31, 2005 9:57 am Post subject:

Novice

Joined: 16 Jan 2004
Posts: 23

Posted: Mon Jan 31, 2005 5:11 pm Post subject:

Padawan

Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.

What's the date format of the database element? You may need to utilize the SQL 'to_date' conversion function.

brenner

Posted: Tue Feb 01, 2005 12:43 am Post subject:

Newbie

Joined: 22 Oct 2004
Posts: 7
Location: IBM Hursley

What level of MQSI and Oracle ?

brenner

Posted: Tue Feb 01, 2005 1:31 am Post subject:

Newbie

Joined: 22 Oct 2004
Posts: 7
Location: IBM Hursley

Please see the wmqi readme file, defect 31642 for a work around using PASSTHRU.
Else you'll have to remove the date from the where clause and then
filter the result set from the select within your ESQL. (loop through the
result set comparing the dates and deleting the elements with non-matching ones. As you are deleting from an array and the deletiion will move the elements up, start from the end and work your way back to the 1st)

brenner

Posted: Tue Feb 01, 2005 1:47 am Post subject:

Newbie

Joined: 22 Oct 2004
Posts: 7
Location: IBM Hursley

and a better option is to write an Oracle stored procedure that takes
2 imput parameters and returns a result set. Then you can use
PASSTHRU to invoke the stored procedure and get the result set that
you want.

cvanmook

Posted: Tue Feb 01, 2005 7:32 am Post subject:

Novice

Joined: 16 Jan 2004
Posts: 23

Thanx, for reporting that it is a bug. We've used the PASSTRU with a TO_DATE and that works fine.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle where clause with Date function

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP