| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Extract Date field from database |
« View previous topic :: View next topic » |
| Author |
Message
|
| Bala |
 Posted: Tue Aug 19, 2003 11:47 am Post subject: Extract Date field from database |
 |
|
Novice
Joined: 14 May 2002
Posts: 12
Location: Chennai, India
|
Hi,
I used the following E-SQL to get the date from Oracle D.B.
DECLARE FRZSTART DATE;
SET Output.LocalEnvironment.XML.MSG.FRZSTART[] = (select DB.FRZSTART from Database.STSC.LOC AS DB
WHERE ("ITEM" = "InputBody"."SAP.IC.E2MDSTL001"."SAP.IF.E2MDSTL001"."SAP.E2MDSTL001.MATNR"
and "LOC" = "Body"."SAP.IC.E2MDSTL001"."SAP.IF.E2MDSTL001"."SAP.E2MDSTL001.WERKS");
SET FRZSTART = SUBSTRING(Output.LocalEnvironment.XML.MSG.FRZSTART[1].FRZSTART FROM 1 FOR 10);
For this i get 5 date fields but all of them are "NULL".
1. Can any one tell me why it is passing NULL when the field exists in the database.
2. How to get the data values from a database.
Regards
Bala
_________________
IBM Certified Specialist MQSeries. |
|
| Back to top |
|
 |
| kirani |
| Posted: Tue Aug 19, 2003 9:21 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Bala,
There are couple of problems with your code. Please try following code and see if it works,
| Code: |
SET Environment.Variables.MSG.FRZSTART[] = (select DB.FRZSTART from Database.STSC.LOC AS DB
WHERE
DB.ITEM = "InputBody"."SAP.IC.E2MDSTL001"."SAP.IF.E2MDSTL001"."SAP.E2MDSTL001.MATNR" AND
DB.LOC = "InputBody"."SAP.IC.E2MDSTL001"."SAP.IF.E2MDSTL001"."SAP.E2MDSTL001.WERKS");
SET FRZSTART = SUBSTRING(Environment.Variables.MSG.FRZSTART[1].FRZSTART FROM 1 FOR 10);
|
could you post a sample value stored into FRZSTART field into DB? When trying to create date data type from STRING data, first you need to create a date string into this format, DATE 'CCYY-MM-DD' and then use CAST function to convert it to DATE data type.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Bala |
| Posted: Wed Aug 20, 2003 5:47 am Post subject: |
|
|
Novice
Joined: 14 May 2002
Posts: 12
Location: Chennai, India
|
Hi Kiran,
I had the peculiar problem that when i am passing the local environment variable in the form of XML message
Environment.variables.MSG.FRZSTART[] ..... I am able to see the values as shown below.
<MSG>
<FRZSTART>2003-08-20 00:00:00</FRZSTART>
</MSG>
But when i am trying to pass the value to say a character variable
DECLARE OUTPUT CHAR;
SET OUTPUT = Environment.variables.MSG.FRZSTART[1]
Still it is passing NULL.
The Date Values are getting passed onto the Scalar variables. But when trying to pass those data values into a varibale is where the problem is!!!!
_________________
IBM Certified Specialist MQSeries. |
|
| 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
|
|
|
|
