| Author |
Message
|
| hariraman |
 Posted: Sun Mar 14, 2004 4:11 pm Post subject: Date Issue |
 |
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
Hi,
I'm Hari, part of the MQSI interface development team. I have a question regarding date manipulation using MQSI/ESQL. This requirement is to add a particular interval to a date value received from an IDOC. ( For ex. if the date returned by IDOC is 20040223, & the look-up value interval is 2, the output should be generated as 20040225.)
When i try to use the code shown below, i'm getting an error like this:
BIP2402E: (184, 56) : Syntax error : 'integer TRIM'.
The token caused a syntax error.
Correct the syntax of your expression and redeploy the message flow.
===============================================
CODE
DELARE EINDT__HDR_WH_ARV_DATE DATE;
DELARE SLFDT__HDR_WH_ARV_DATE DATE;
IF KEY_VAL IS NOT NULL THEN
SET DT_LOOKUP_VAL =
THE (
SELECT ITEM LK.VALUE
FROM Database.LOOKUP AS LK
WHERE LK.TABLE_NAME = 'ATLAS_TO_DW_ARRIVAL_LEAD_MFANZ'
AND LK.KEY = KEY_VAL
);
END IF;
SET EINDT__HDR_WH_ARV_DATE = TRIM(InputRoot.XML.ZHUEXT01.IDOC.Z1HUEXH.EINDT);
SET SLFDT__HDR_WH_ARV_DATE = InputRoot.XML.ZHUEXT01.IDOC.Z1HUEXH.SLFDT;
SET ZHUSTAT__COMPARE = InputRoot.XML.ZHUEXT01.IDOC.Z1HUEXH.ZHUSTAT;
IF ZHUSTAT__COMPARE IN ('1','2') THEN
SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL DT_LOOKUP_VAL DAY;
SET OutputRoot.XML.recordset.HDR_RECORD.HDR_WH_ARV_DATE
= EINDT__HDR_WH_ARV_DATE;
END IF;
===============================================
The above code works well when it's written like below:
SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL '2' DAY;
Kindly review the code and suggest me how to manipulate the date using ESQL. Also send me, if you have any sample code for our understanding.
Thanks,
Regards,
Hari |
|
| Back to top |
|
 |
| Missam |
| Posted: Sun Mar 14, 2004 6:39 pm Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
| How did you Declared the Varaible DT_LOOKUP_VAL Is it Integer or Character.If its Integer you need to CAST it to Character Before Supplying To INTERVAL Function |
|
| Back to top |
|
|
| hariraman |
| Posted: Sun Mar 14, 2004 8:21 pm Post subject: Date Issue |
|
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
The variable DT_LOOKUP_VAL is declared as a CHAR (Character) variable.
Thanks,
Hari |
|
| Back to top |
|
|
| Missam |
| Posted: Mon Mar 15, 2004 7:11 am Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
whats your 184 th line which is showing a syntax error.is it the same line
| Quote: |
SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL DT_LOOKUP_VAL DAY;
|
|
|
| Back to top |
|
|
| Missam |
| Posted: Mon Mar 15, 2004 7:32 am Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
Hai
When i'm working with your code both statements
| Code: |
| SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL DT_LOOKUP_VAL DAY; |
And
| Code: |
SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL '2' DAY;
|
Gave me the Syntax Error.but when i'm using EVAL i didn't see any errors.
| Code: |
| EVAL('SET EINDT__HDR_WH_ARV_DATE = DATE' || EINDT__HDR_WH_ARV_DATE ||'+ INTERVAL' || DT_LOOKUP_VAL || 'DAY'); |
I don't know how your code
| Code: |
SET EINDT__HDR_WH_ARV_DATE = DATE EINDT__HDR_WH_ARV_DATE INTERVAL '2' DAY;
|
worked with out a + operand to add an INTERVAL to DATE |
|
| Back to top |
|
|
| hariraman |
| Posted: Mon Mar 15, 2004 9:22 pm Post subject: Date Issue |
|
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
Hi IamSam,
Thanks for your reply. Still i couldn't get thru' in the date manipulation problem. The suggestion you made as follows gives out an error 'Not a valid Compute Expression'.
EVAL('SET EINDT__HDR_WH_ARV_DATE = DATE' || EINDT__HDR_WH_ARV_DATE ||'+ INTERVAL' || DT_LOOKUP_VAL || 'DAY');
Also in my previous method, i used the following code which worked for a static value:
SET EINDT__HDR_WH_ARV_DATE = CAST((MYDATE + INTERVAL '9' DAY) AS CHAR);
Appreciate your further help on this problem. Thanks,
Regards,
Hari |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 16, 2004 4:25 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You should not use EVAL unless you absolutely have to. It's a big performance hit.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| hariraman |
| Posted: Tue Mar 16, 2004 2:23 pm Post subject: Date Issue |
|
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
Thanks Jeff for your suggestion on the performance. Definitely it should be a value add to skip from using EVAL. But considering this situation, i really need a work around to somehow need to get a solution.
Any other suggestions...?
Let me know.
Best Regards,
Hari |
|
| Back to top |
|
|
| EddieA |
| Posted: Tue Mar 16, 2004 5:16 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Hari,
In order to do this, you're going to have to take your input date, and 'build' a date in the form '2004-02-23' using substring and concatenate. This can then be cast as a Date data type. You can then add (+) to this, your interval, which has been cast from a character to an Interval data type. The resultant date, can then be cast back to Character and substringed to get the format you want.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| hariraman |
| Posted: Tue Mar 16, 2004 6:17 pm Post subject: Date Issue |
|
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
Hi Eddie,
Thanks for your suggestion. The input date format is 2004-02-24 only. So, i don't think it may be the problem. Also i have already tried the casting of input into date format. If you read the conversion again, you could grasp more on the problem in-detail. In that, i had referred that, if i give the interval value as a 'hard-coded input', the code was working, else if i give the interval value thru' a variable, it gives an error. Hope you understand what i mentioned above.
Let me know if you find anyother way around to solve this.
Regards,
Hari |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Mar 17, 2004 2:01 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| Quote: |
| 'build' a date in the form '2004-02-23' using substring and concatenate |
| Code: |
DECLARE TodayDate CHARACTER;
SET TodayDate = '2004-03-17'; |
| Quote: |
| This can then be cast as a Date data type |
| Code: |
| CAST(TodayDate AS DATE) |
| Quote: |
| your interval, which has been cast from a character to an Interval data type |
| Code: |
DECLARE OneWeek CHARACTER;
SET OneWeek = '7';
CAST(OneWeek AS INTERVAL DAY) |
| Quote: |
| You can then add (+) |
| Code: |
| CAST(TodayDate AS DATE) + CAST(OneWeek AS INTERVAL DAY) |
| Quote: |
| The resultant date, can then be cast back to Character and substringed to get the format you want. |
| Code: |
| CAST(CAST(TodayDate AS DATE) + CAST(OneWeek AS INTERVAL DAY) AS CHARACTER); |
| Code: |
(
(0x3000000)TodayDate = '2004-03-17'
(0x3000000)OneWeek = '7'
(0x3000000)NextWeek = '2004-03-24'
) |
Questions. 
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Thu Mar 18, 2004 11:23 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
When using a create procedure () external "schema.spname" why is timestamp as a inout parameter. Is this defined as a ESQL Reference Type. The assumption is yes, and if so I can not use it using create procedure(). If a stored procedure expects a timestamp format how should i define it in the create procedure()?
Since I have not came across a situation where timestamp was required as an input/output parm via create procedure this is the first time I have seen this issue.
Chris |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Thu Mar 18, 2004 12:40 pm Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
Resolved the problem.
Created two variables.
DECLARE VarTimeStamp TIMESTAMP;
DECLARE InOutTimeStamp CHARACTER;
SET VarTimeStamp = CURRENT_TIMESTAMP;
SET InOutTimeStamp = SUBSTRING(CAST(VarTimeStamp AS CHARACTER) FROM 11 FOR 26);
CALL MySP (InOutTimeStamp);
CREATE PROCEDURE MySP (INOUT MYTIMESTAMP) EXTERNAL "Yup.Yup"; |
|
| Back to top |
|
|
| hariraman |
| Posted: Sun Mar 21, 2004 7:00 pm Post subject: Date Issue |
|
|
Novice
Joined: 24 Jul 2001
Posts: 16
Location: Chennai,India
|
Hi Eddie,
Thanks for your brilliant suggestion, it helped me to solve the problem. I didn't get the idea of splitting the cast operation of Date portion and Interval separtely. Your idea worked well.
My thanks again to your support.
Regards,
Hari |
|
| Back to top |
|
|
|
|
