| Author |
Message
|
| sammy_910 |
 Posted: Wed Jan 12, 2011 1:17 am Post subject: Question on ROW datatype in ESQL |
 |
|
Apprentice
Joined: 16 Dec 2010
Posts: 33
|
Hi All,
I am getting a set of records from my SELECT statement below:
DECLARE EXCEPTION_DETAILS ROW;
SET EXCEPTION_DETAILS.Message[] =
SELECT * FROM Database.TEMP AS T3 WHERE 'SomeCondition';
Since I need to find the number of records and then refer to each record at a time.
SET msgCount = CARDINALITY(EXCEPTION_DETAILS.Message[]);
DECLARE tempMsgRef REFERENCE TO (EXCEPTION_DETAILS.Message[1]);
But when I deploy this code it gives me a deployment error:
: Illegal data type for target. A list field reference is required.
The expression supplying the target must evaluate to a value of a suitable type. The given expression cannot possibly do so.
How can I then refer to the tree structure formed in my ROW variable?Please throw some light on this. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Jan 12, 2011 5:50 am Post subject: Re: Question on ROW datatype in ESQL |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| sammy_910 wrote: |
| How can I then refer to the tree structure formed in my ROW variable? |
It's hanging under your ROW variable. Look in the debugger or take a trace.
You'll need something like EXCEPTION_DETAILS.Message.*[]
Or something. A little experimentation & the debugger will get you there.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| eugen |
| Posted: Wed Jan 12, 2011 11:35 pm Post subject: |
|
|
Novice
Joined: 28 Dec 2010
Posts: 22
|
EXCEPTION_DETAILS.Message.*[] - should do the trick as Vitor said
Thx,
Eugene. |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jan 14, 2011 1:52 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
OK, so first, posting a snippet of code that is complete helps, as it makes it easier to see what is going on 
Second,
| Code: |
| EXCEPTION_DETAILS.Message.*[] |
actually counts everything below Message which is the number of columns returned for one row, not the total number of rows which I think is what was wanted.
Third, the code below which is complete, and does compile (simply changed to use a message rather than a DB as it was quicker) does what I believe is being asked for:
| Code: |
DECLARE EXCEPTION_DETAILS ROW;
DECLARE msgCount INTEGER -1;
SET EXCEPTION_DETAILS.Message[] = SELECT * FROM InputRoot.XML.Top.a[] AS T3 WHERE T3 = 44;
SET msgCount = CARDINALITY(EXCEPTION_DETAILS.Message[]);
DECLARE tempMsgRef REFERENCE TO EXCEPTION_DETAILS.Message[1];
SET OutputRoot.XMLNSC.Top.Out1 = msgCount;
SET OutputRoot.XMLNSC.Top.Out2 = tempMsgRef;
SET OutputRoot.XMLNSC.Top.Out3 = EXCEPTION_DETAILS; |
Given this input message:
| Code: |
| <Top><a>44</a><b>49</b><a>44</a><a>40</a><a>45</a><a>42</a></Top> |
It produces this output:
| Code: |
| <Top><Out1>2</Out1><Out2>44</Out2><Out3><Message>44</Message><Message>44</Message></Out3></Top> |
I hope this helps,
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sammy_910 |
| Posted: Sun Jan 16, 2011 11:02 pm Post subject: |
|
|
Apprentice
Joined: 16 Dec 2010
Posts: 33
|
Thanks Vitor and Eugene,
| Quote: |
EXCEPTION_DETAILS.Message.*[]
|
works fine to return the number of columns in the table as said by mgk.
for me EXCEPTION_DETAILS.*[] gives me the number of records returned. 
But now my problem is how do i create a reference to each record in the Row variable.
My code is something like this:
DECLARE EXCEPTION_DETAILS ROW;
SET EXCEPTION_DETAILS.Message[] =
SELECT * FROM Database.MESSAGE_EXCEPTION AS T3
WHERE T3.REPLAY_RULE = 'A' AND (T3.STATUS = '0' OR T3.STATUS = 'P');
SET exceptionCount = CARDINALITY(EXCEPTION_DETAILS.*[]);
SET msgCount = CARDINALITY(EXCEPTION_DETAILS.Message.*[]);
COUNTLOOP : WHILE (exceptionIndex <= exceptionCount) DO
DECLARE tempExcepMsgRef REFERENCE TO (EXCEPTION_DETAILS.Message[exceptionIndex]);
SET REQUEST_TYPE = tempExcepMsgRef.REQUEST_TYPE;
SET exceptionIndex = exceptionIndex + 1;
END WHILE COUNTLOOP;
This again gives me a deployment err of Invalid field reference. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jan 17, 2011 1:56 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
Please look again at the code I posted - I already showed how to do this. Your code here:
| Code: |
| DECLARE tempExcepMsgRef REFERENCE TO (EXCEPTION_DETAILS.Message[exceptionIndex]); |
Is wrong as it has brackets after the TO. It should be:
| Code: |
| DECLARE tempExcepMsgRef REFERENCE TO EXCEPTION_DETAILS.Message[exceptionIndex]; |
Also, I think your loop is not the best way to do this. At the moment you are declaring a new reference each time around the loop. The best way would be to not use cardinality at all in this case, and simply use the reference variable to do the loop checking lastmove each time...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sammy_910 |
| Posted: Mon Jan 17, 2011 2:36 am Post subject: |
|
|
Apprentice
Joined: 16 Dec 2010
Posts: 33
|
Thanks mgk,
I agree with checking the lastmove value in my while loop.
But again when I create a reference like
DECLARE tempExcepMsgRef REFERENCE TO EXCEPTION_DETAILS.Message[1];
It gives me an deployment error:
Illegal data type for target. A list field reference is required.
Strangely
EXCEPTION_DETAILS.Message[1]
does not seem to be a valid reference. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Jan 17, 2011 2:55 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello
| Quote: |
Strangely
EXCEPTION_DETAILS.Message[1]
does not seem to be a valid reference. |
That's because it is not a list reference. This would be a list reference:
| Quote: |
| EXCEPTION_DETAILS.Message[] |
but this is not what you want. Are you SURE the exception is coming from this line of code? Try deploying the code I gave above just to see if it works for you. It deployed for me, and declares a reference in the same you as you claim gives an error...
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sammy_910 |
| Posted: Wed Jan 19, 2011 2:26 am Post subject: |
|
|
Apprentice
Joined: 16 Dec 2010
Posts: 33
|
Thanks mgk,
It works!!!!  |
|
| Back to top |
|
|
| se_zn2003 |
| Posted: Mon Aug 19, 2013 10:24 pm Post subject: How many records can insert in ROW variable? |
|
|
Apprentice
Joined: 07 May 2013
Posts: 30
|
I use this variable
| Code: |
| DECLARE Var_LiveSessionMP SHARED ROW; |
to insert record in to it.
I use it for session of any users are enter to application.
what this maximum Row can insert in it. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Aug 20, 2013 3:13 am Post subject: Re: How many records can insert in ROW variable? |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| se_zn2003 wrote: |
I use this variable
| Code: |
| DECLARE Var_LiveSessionMP SHARED ROW; |
to insert record in to it.
I use it for session of any users are enter to application.
what this maximum Row can insert in it. |
Don't open old posts. This post was two years old. Do your own work. If you need to post, create your own post and describe what work you have done. I'm sure after two years, the OP and all who commented, have figured out what they want.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
