| Author |
Message
|
| learnWMB_abc |
 Posted: Sat Nov 09, 2013 5:22 am Post subject: issue with SELECT statement on message tree |
 |
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
WMB broker v6.1.0.11(yeah!)
I have a message flow
MQInput-Compute- SOAPRequest-Compute1-MQOutput
In Compute1 node, I am trying to map the web service response to output tree.
some of the response elements from webservice response need to be mapped to the output
| Code: |
DECLARE I INTEGER;
SET I = 1;
CREATE FIELD OutputRoot.XMLNS.a.b[I];
DECLARE outRef REFERENCE TO OutputRoot.XMLNS.a.b.[I];
DECLARE InRef REFERENCE TO InputRoot.A.B.C ;
SET outRef =(SELECT R.A1 AS FirstElement, R.A2 AS secondElement, R.A3 AS ThirdElement FROM outRef.D[] AS R WHERE R.E1='string1' ); |
This fails at the deployment with the error
"BIP2497E: Illegal data type for target. A list field reference is required. "
because I have given OutRef instead of something like OutRef.a[]
But my requirement is to output the value to the exact occurance of variable I..
because I will be having a 'for loop' for 'I' outside this code snippet.
My expected output has to be something like this..
<a>
<b>
<FirstElement>a</FirstElement>
<secondElement>b</secondElement>
<ThirdElement>c</ThirdElement>
</b>
<b>
<FirstElement>d</FirstElement>
<secondElement>e</secondElement>
<ThirdElement>f</ThirdElement>
</b>
<b>
<FirstElement>g</FirstElement>
<secondElement>h</secondElement>
<ThirdElement>i</ThirdElement>
</b>
</a>
So I cannot code like this
| Code: |
| SET OutputRoot.XMLNS.a.b[]=(SELECT .....) |
Ofcourse I tried the normal method of inner looping for D[] which is working fine.(ie without SELECT statement. )
which will loop on D[] , get the value, assign to a variable and finally assign to output tree outside the loop)
But I thought if we can get it with one SELECT statement, it will be faster and efficient..
Any suggestions on how to overcome this issue?
(This is my first post!, So if it is not conveying the message properly,, plz dont hesitate to write 'Okay. I dont get what u r trying to do' )
Thanks |
|
| Back to top |
|
 |
| learnWMB_abc |
| Posted: Sat Nov 09, 2013 5:36 am Post subject: |
|
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
few typos there
I am actually replacing the orginal code xpaths and varaibles with sample names here.. that is y
| Code: |
DECLARE outRef REFERENCE TO OutputRoot.XMLNS.a.b[I]; -- not b.[I]
DECLARE InRef REFERENCE TO InputRoot.SOAP.A.B.C ; ---- 'SOAP' was not mentioned b4
|
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Nov 09, 2013 1:25 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
you seem to have correctly understood some of the response but mistinterpreted it...
What you want may be something like
| Code: |
set outputref[]= (your select here) or
set outputref.[] = (your select here) |
Note the brackets on the left of the = sign.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| learnWMB_abc |
| Posted: Sat Nov 09, 2013 10:45 pm Post subject: |
|
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
nope. that doesnt work.
So after this code,
| Code: |
DECLARE I INTEGER;
SET I = 1;
CREATE FIELD OutputRoot.XMLNS.a.b[I];
DECLARE outRef REFERENCE TO OutputRoot.XMLNS.a.b[I];
DECLARE InRef REFERENCE TO InputRoot.SOAP.A.B.C ; |
if I write
SET outRef =(the select statement).--- this is error at deployment as mentioned b4
| Code: |
| SET outRef[]=(the select statement)-- this errors at compilation itself(expected) |
| Quote: |
Severity and Description Path Resource Location Creation Time Id
Syntax error. Valid options include: identifier ) } ] , || / = >= > <= < - <> ( . + ; *
AFTER AND AS BEFORE BEGIN CALL CASE CATALOG CREATE DAY
DECLARE DEFAULT DELETE DO DOMAIN ELSE ELSEIF END ESCAPE EXCEPTION
FOR FROM HOUR IDENTIFIER IDENTITY IF IN INSERT INTO IS
ITERATE LEAVE LIKE LOOP MINUTE MODE MONTH NOT OR REPEAT
RETURN SECOND THEN TO UNTIL VALUES WHEN WHERE WHILE YEAR abcMFP abcMF.esql line 134 1384064960203 12605 |
| Code: |
SET outRef.[]=(the select statement)--this deploys fine but doesnot assign the value to output tree |
| Code: |
| SET outRef.a[]=(the select statement)-- this deploys fine, assigns value under a[] but this is not what we want. |
|
|
| Back to top |
|
|
| dogorsy |
| Posted: Sun Nov 10, 2013 12:06 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
1- use the XMLNSC parser.
2- you do not need to code
| Code: |
DECLARE I INTEGER;
SET I = 1;
|
3-
| Code: |
CREATE FIELD OutputRoot.XMLNSC.a;
DECLARE outRef REFERENCE TO OutputRoot.XMLNSC.a; |
of if you prefer, use the AS clause in the CREATE FIELD. Look up the syntax in the infoCentre. Something like
| Code: |
CREATE FIELD OutputRoot.XMLNSC.a AS outRef;
|
4-
| Code: |
SET outRef.b[] = ( your select statement ). |
|
|
| Back to top |
|
|
| dogorsy |
| Posted: Sun Nov 10, 2013 10:01 pm Post subject: Re: issue with SELECT statement on message tree |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| learnWMB_abc wrote: |
So I cannot code like this
| Code: |
| SET OutputRoot.XMLNS.a.b[]=(SELECT .....) |
|
YES YOU CAN. And after that you can either use CARDINALITY or
a FOR loop with the AS keyword. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Nov 10, 2013 10:46 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| learnWMB_abc wrote: |
| Code: |
| SET outRef.a[]=(the select statement)-- this deploys fine, assigns value under a[] but this is not what we want. |
|
What exactly do you want?
Everything you need has been shown to you so if we are missing something please tell us.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| learnWMB_abc |
| Posted: Mon Nov 11, 2013 8:47 am Post subject: |
|
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
I will try to rewrite the problem statement.
The expected output should be like this.
<a>
<b>
<FirstElement>a</FirstElement>
<secondElement>b</secondElement>
<ThirdElement>c</ThirdElement>
</b>
<b>
<FirstElement>d</FirstElement>
<secondElement>e</secondElement>
<ThirdElement>f</ThirdElement>
</b>
<b>
<FirstElement>g</FirstElement>
<secondElement>h</secondElement>
<ThirdElement>i</ThirdElement>
</b>
-- b[] can come any number of times.
</a>
as you can see from the xml,
I want to output the firstelement,secondElement and thirdelement DIRECTLY under element b.
and these 3 values are going to come from the soap response.
So I want to do a SELECT statement on the soap response which will populate these three elements directly under b
Now how to attain that?
| Code: |
DECLARE I INTEGER;
SET I = 1;
WHILE(I<10) DO
CREATE FIELD OutputRoot.XMLNS.a.b[I];
DECLARE outRef REFERENCE TO OutputRoot.XMLNS.a.b[I];
DECLARE InRef REFERENCE TO InputRoot.SOAP.A.B.C[i] ;
--HERE write the select statement to map from input to output., This statement is what we are discussing about here.
SET I=I+1 ;
END WHILE
|
So how we write that SELECT statement is the issue here.
So I tried first
SET outRef =(the select statement)-- that failed
So fjb_saper said to try 2 options
SET outRef[] =(the select statement)-- that fails
SET outRef.[]= (the select statement)-- that also fails
Now smdavies99 is asking what is wrong with SET outRef.d[] =(the select statement) which will work without errors
If i code like that,
the output will be like this
<a>
<b>
<d>
<FirstElement>a</FirstElement>
<secondElement>b</secondElement>
<ThirdElement>c</ThirdElement>
</d>
</b>
<b>
<d>
<FirstElement>d</FirstElement>
<secondElement>e</secondElement>
<ThirdElement>f</ThirdElement>
</d>
</b>
<b>
<d>
<FirstElement>g</FirstElement>
<secondElement>h</secondElement>
<ThirdElement>i</ThirdElement>
</d>
</b>
-- b[] can come any number of times.
</a>
plz see that ,now firstelement,secondElement and thirdelement are NOT coming DIRECTLY under element b, it is coming one step down from 'b', it comes under 'd' now
I hope this is clear . |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Nov 11, 2013 8:50 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
You DO NOT NEED TO RESTATE the problem. I have given the answer. You are not reading it.
| Code: |
| SET OutputRoot.XMLNSC.a.b[]=(SELECT .....) |
|
|
| Back to top |
|
|
| learnWMB_abc |
| Posted: Mon Nov 11, 2013 9:20 am Post subject: |
|
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
Thanks
but no
let me show you by a correct example output
| Quote: |
<a>
<b>
other elements here
<FirstElement>a</FirstElement>
<secondElement>b</secondElement>
<ThirdElement>c</ThirdElement>
other elements here
</b>
<b>
other elements here
</b>
<b>
other elements here
<FirstElement>g</FirstElement>
<secondElement>h</secondElement>
<ThirdElement>i</ThirdElement>
</b>
</a> |
First,second, third elements are proper for B[1]
these 3 elements not to be assinged for B[2] because select statement returned 0 rows in second case
First,second, third elements are proper for B[3]
u see, there are no first,second,thirdselements for B[2]
In this case, If I code like what you told(ofcourse , i have to remove the WHERE conditions on the select statement to have multiple set of outputs so as to output all of them to b[]).
the output will be like this
| Quote: |
<a>
<b>
other elements here
<FirstElement>a</FirstElement>
<secondElement>b</secondElement>
<ThirdElement>c</ThirdElement>
other elements here
</b>
<b>
other elements here
<FirstElement>g</FirstElement>
<secondElement>h</secondElement>
<ThirdElement>i</ThirdElement>
</b>
<b>
other elements here
</b>
</a> |
u see, if i output all of them in one statement, it will be like
First,second, third elements are proper for B[1]
worngly assigned for B[2]
because this code u are saying will assign B[] in full in 1,2,3 order
not assigned for B[3]
basically, the SELECT statement should return only no row or one row only for this case so that it can be outputted at correct occurance of B[I] |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Nov 11, 2013 9:34 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Let the select return al lthe rows.
This will be in an incorrect structure so then restructure it in a for/while loop.
A little experimentation should crack it.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Nov 11, 2013 9:34 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
Don't say NO.
That is the way the select statement works. Whether you like it or not.
And also, you have changed the output, in the original post and in the restatement, you did not have "other elements here".
So, if you do not give an exact explanation of what you want to achieve, then you will get nowhere. |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Nov 11, 2013 9:44 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Try using the INSERT clause of the SELECT statement (instead of the AS clause):
| Quote: |
INSERT selections
The INSERT clause is an alternative to the AS clause. It assigns the result of the SelectClause expression (which must be a row) to the current new row itself, rather than to a child of it. The effect of this is to merge the row result of the expression into the row being generated by the SELECT. This differs from the AS clause, in that the AS clause always generates at least one child element before adding a result, whereas INSERT generates none. INSERT is useful when inserting data from other SELECT operations, because it allows the data to be merged without extra folders. |
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 |
|
|
| learnWMB_abc |
| Posted: Mon Nov 11, 2013 9:49 am Post subject: |
|
|
Newbie
Joined: 09 Nov 2013
Posts: 6
|
dogorsy-
My sincere apologies if that meant offensive
I meant to write,,'but not working' but ended up 'but no' which gave a whole diff meaning i think
Please excuse my English .-- wont happen again.
As mentioned in first post, I was really looking to avoid that using one more loop there. because I was already able to achieve this using standard looping method and was trying with select statements.
dogorsy- I also dont think "other elements here" made a difference to logic . I just gave it to have a better look there. anyway thnx for the comments. |
|
| Back to top |
|
|
|
|
