| Author |
Message
|
| Bravo |
 Posted: Mon Aug 28, 2006 4:28 pm Post subject: Help required in SELECT statement |
 |
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Hi All,
I am using WBIMB ver5 in AIX and I have problem in selecting a list of TESTID.
Here is the input :
| Code: |
<?xml version="1.0" encoding="UTF-8"?>
<NS1:Temp01.xmlns:NS1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01" version="3.0.0" verb="Create" locale="en_US" delta="false">
<NS1:Control_record>
<NS4:idoc xmlns:NS4="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/idoc" version="3.0.0" verb="" locale="en_US" delta="false">
<NS4:IDoc_number>123456789</NS4:IDoc_number>
</NS4:idoc>
</NS1:Control_record>
<NS1:Data_record>
<NS3:Temp01_data xmlns:NS3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_data" version="3.0.0" verb="" locale="en_US" delta="false">
<NS3:Temp01_test000 size="3">
<NS2:Temp01_test000 xmlns:NS2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_test000" version="3.0.0" verb="" locale="en_US" delta="false">
<NS2:TESTID>2222222222</NS2:TESTID>
<NS2:SNO>000010</NS2:SNO>
</NS2:Temp01_test000>
<NS2:Temp01_test000 xmlns:NS2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_test000" version="3.0.0" verb="" locale="en_US" delta="false">
<NS2:TESTID>3333333333</NS2:TESTID>
<NS2:SNO>000010</NS2:SNO>
</NS2:Temp01_test000>
<NS2:Temp01_test000 xmlns:NS2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_test000" version="3.0.0" verb="" locale="en_US" delta="false">
<NS2:TESTID>9999999999</NS2:TESTID>
<NS2:SNO>000010</NS2:SNO>
</NS2:Temp01_test000>
</NS3:Temp01_test000>
</NS3:Temp01_data>
</NS1:Data_record>
</NS1:Temp01>
|
Here is the esql code
| Code: |
SET Environment.Variables.TEST[] = (SELECT InputBody.Temp01:Data_record.
Temp01_data:Temp01_data.
Temp01_data:Temp01_test000.
Temp01_test000:Temp01_test000.
Temp01_test000:TESTID
FROM InputBody.Temp01:Data_record.
Temp01_data:Temp01_data.
Temp01_data:Temp01_test000.Temp01_test000:Temp01_test000[]);
|
OR
| Code: |
DECLARE inRef REFERENCE TO "InputBody".Temp01:Data_record.
Temp01_data:Temp01_data.
Temp01_data:Temp01_test000;
SET Environment.Variables.TEST[] = (SELECT inRef.Temp01_test000:Temp01_test000.Temp01_test000:TESTID
FROM "InputBody".Temp01:Data_record.
Temp01_data:Temp01_data.
Temp01_data:Temp01_test000.Temp01_test000:Temp01_test000[]);
|
The output result is
Environment.Variables.TEST[1] = 2222222222
Environment.Variables.TEST[2] = 2222222222
Environment.Variables.TEST[3] = 2222222222
Expected result should be
Environment.Variables.TEST[1] = 2222222222
Environment.Variables.TEST[2] = 3333333333
Environment.Variables.TEST[3] = 9999999999
I have tried the another way but in vain
| Code: |
SET Environment.Variables.TEST[] = (SELECT C.VBELN
FROM "InputBody".Temp01:Data_record.
Temp01_data:Temp01_data.
Temp01_data:Temp01_test000.Temp01_test000:Temp01_test000[]); AS C);
|
O/p is with no value
Environment.Variables.TEST[1] =
Environment.Variables.TEST[2] =
Environment.Variables.TEST[3] =
Please let me know your suggestions.
_________________
Bravo |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Mon Aug 28, 2006 8:17 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Bravo,
Try this (not tested):
| Code: |
DECLARE NS1 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01';
DECLARE NS2 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_test000';
DECLARE NS3 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01_data';
DECLARE inRef REFERENCE TO "InputBody".NS1:Temp01.NS1:Data_record.NS3:Temp01_data.NS3:Temp01_test000;
SET Environment.Variables.TEST[] = (SELECT A.NS2:TESTID FROM inRef.NS2:Temp01_test000[] AS A); |
I'm not very sure about the select statement....check that....
I pasted your message in XMLSpy, it did not seem well formed....check that too.
Let us know if it worked.
Regards. |
|
| Back to top |
|
|
| Bravo |
| Posted: Tue Aug 29, 2006 2:22 pm Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Wow..works gr8..I can see all the three values.Thanks elvis_gn
I am facing another problem.I am trying to execute the statement but I'm not getting result to the destination Environment variable from the earlier input.
Here is the esql
| Code: |
DECLARE NS1 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01';
DECLARE NS2 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/idoc';
SET Environment.Variables.idocInfo = (SELECT A.NS2:IDoc_number FROM InputBody.NS1:Control_record.NS2:idoc AS A);
SET Environment.Variables.Inumber = Environment.Variables.idocInfo.IDoc_number;
|
I can see the value in debug mode for the Environment.Variables.idocInfo.IDoc_number but if I tried to assign to Environment.Variables.Inumber.EV is not created with value.
Expected o/p should be
Environment.Variables.Inumber = 123456789
Any help!!
_________________
Bravo |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Aug 29, 2006 2:24 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You need THE and ITEM
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Bravo |
| Posted: Tue Aug 29, 2006 2:50 pm Post subject: |
|
|
Centurion
Joined: 03 Oct 2005
Posts: 146
|
Works perfect!! But the client now added one more field in the schema.If I tried to do more than one field in a select with THE and ITEM, it throws me an error.
for eg
| Code: |
DECLARE NS1 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/Temp01';
DECLARE NS2 NAMESPACE 'http://www.ibm.com/websphere/crossworlds/2002/BOSchema/idoc';
SET Environment.Variables.idocInfo = (SELECT A.NS2:IDoc_number, A.NS2.status FROM InputBody.NS1:Control_record.NS2:idoc AS A);
|
How can I achieve this..
_________________
Bravo |
|
| Back to top |
|
|
|
|
