| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Exact date comparision and retrival from DB |
« View previous topic :: View next topic » |
| Author |
Message
|
| rsk33 |
 Posted: Sun Sep 23, 2012 4:34 am Post subject: Exact date comparision and retrival from DB |
 |
|
Centurion
Joined: 21 Aug 2006
Posts: 141
|
Hi,
I am trying to retrieve the date from oracle db by comparing it with the input xml request string.
in db the coumn is TIMESTAMP and i have the input data in YYYY-MM-DD 00:10:00 and exactly comare the date and retrive it and tried with the below code but not successful. Would appreciate any help.
DECLARE DtStr CHAR;
SET DtStr = InputRoot.XMLNSC.TEST.PrcDt
SET DtStr = SUBSTRING(DtStr FROM 1 FOR 10);
SET Dt = CAST(DtStr AS DATE);
i tried Dtstr as char,casting as Timestamp, and as date but not successful. would appreciate help.
DECLARE pat CHAR 'YYYY-MM-DD';
SET Query = 'SELECT * from TAB CAST(CREATION_DATE AS DATE FORMAT pat) =?';
SET Environment.variables.DATA[] = PASSTHRU(Query,Dt);
SET Environment.variables.DATA[] = PASSTHRU(Query,DtStr); |
|
| Back to top |
|
 |
| rsk33 |
| Posted: Sun Sep 23, 2012 6:45 am Post subject: |
|
|
Centurion
Joined: 21 Aug 2006
Posts: 141
|
I got the sql work around with EVAL but passthru still throwing errors.
DECLARE prcDt CHAR;
SET prcDt = InputRoot.XMLNSC.TEST.prcdt
SEt prcDt = SUBSTRING(prcDt FROM 1 FOR 10); -- YYYY-MM-DD format
-- working
SET Query = 'SELECT * from Database.TEST AS T where CAST(T.TEST_DATE AS DATE FORMAT ' || '''' || pat || ''''|| ')=' || '''' || prcDt || '''';
SET Environment.variables.DATA[] = EVAL(Query);
-- [IBM][ODBC Oracle driver][Oracle]ORA-00907: missing right parenthesis
SET Query = 'SELECT * from TEST where CAST(T.TEST_DATE AS DATE FORMAT ' || '''' || pat || ''''|| ')=' || '''' || prcDt || '''';
SET Environment.variables.DATA[] = PASSTHROUGH(Query,prcDt); |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Sep 23, 2012 6:01 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Presumably you tried the easy things first:
| Code: |
--notice I'm using code tags? see how it's easy to read?
Set Environment.Variables.DATA[] = SELECT T.* From Database.TEST as T where T.TEST_DATE = InputRoot.XMLNSC.Test.PrcDT; |
And then tried the slightly less simple
| Code: |
--code tags again, ain't it pretty?
Set Environment.Variables.DATA[]=PASSTHRU('Select * from TEST where TEST_DATE=?',InputRoot.XMLNSC.Test.PrcDT); |
If neither of those actually worked, you really should still try to keep the CAST from being passed into the database and evaluated there, but rather execute it separately in your ESQL before you pass the resulting value into the query. Your code samples don't do that - they keep trying to ask the database to perform an ESQL Cast. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
