| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| THE (SELECT ITEM....... |
« View previous topic :: View next topic » |
| Author |
Message
|
| ican.sbyn |
 Posted: Tue Mar 09, 2010 1:01 am Post subject: THE (SELECT ITEM....... |
 |
|
Novice
Joined: 14 Jul 2009
Posts: 19
|
DearAll,
I want to fetch a particular value from Database based on a Timestamp field.
Select TransactionNumber from Database.LookUpTable where LookUpTime = TO_DATE(Somedate, 'MM/DD/YYYY HH24:MI:SS') -- This waht I want to do.
Why 'MM/DD/YYYY HH24:MI:SS' format is used: The reason is, in the Message broker 'Somedate' is comming like '12/18/2009 13:25:00' and in the database table, this value becomes '12/18/2009 1:25:00 PM'.
I am using the following code to make it work:
| Code: |
SET Environment.Var[] = PASSTHRU('SELECT TransactionNumber FROM LookUpTable WHERE
IDNumber=? AND
LookUpTime=TO_DATE(?, ''MM/DD/YYYY HH24:MI:SS'')'
VALUES(IDNum ,
Somedate));
SET vIdTrn = Environment.Var.TransactionNumber ;
|
The code is working fine.
But I want to use "THE (SELECT ITEM......." clause. How the code will look like. Here the main challange is to append the Timestamp format.
| Code: |
SET vIdTrn = THE(SELECT ITEM L.TransactionNumber FROM Database.{dbSchema}.LookUpTable AS B
WHERE B.IDNumber = IDNum ---- Is a Valid Query BUT::::
SET vIdTrn = THE(SELECT ITEM L.TransactionNumber FROM Database.{dbSchema}.LookUpTable AS B
WHERE B.IDNumber = IDNum AND
B.LookUpTime = TO_DATE(Somedate,'MM/DD/YYYY HH24:MI:SS')) --- Is not a VALID ESQL
|
Can any body please let me know the equivalent of the below incorrect ESQL query (Using 'THE (SELECT ITEM....)' :
SET vIdTrn = THE(SELECT ITEM L.TransactionNumber FROM Database.{dbSchema}.LookUpTable AS B
WHERE B.IDNumber = IDNum AND
B.LookUpTime = TO_DATE(Somedate,'MM/DD/YYYY HH24:MI:SS')) |
|
| Back to top |
|
 |
| gs |
| Posted: Fri Mar 12, 2010 5:35 am Post subject: |
|
|
Master
Joined: 31 May 2007
Posts: 254
Location: Sweden
|
TO_DATE is a database function which can not be called directly.
Cast your string to a DATE type first using CAST(yourdate AS DATE FORMAT '<date format>') |
|
| Back to top |
|
|
| tanishka |
| Posted: Fri Nov 19, 2010 5:13 am Post subject: |
|
|
Centurion
Joined: 24 Nov 2008
Posts: 144
|
ican.sbyn,
I got same issue. Did you able to resolve this issue. |
|
| 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
|
|
|
|
