| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| using SELECT..WHERE.. |
« View previous topic :: View next topic » |
| Author |
Message
|
| miron |
 Posted: Mon Jul 15, 2002 6:30 am Post subject: using SELECT..WHERE.. |
 |
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hello,
I am trying to select a value from my XML data using SELECT.. WHERE..
but I am getting this error :
BIP2406E: (14, 60) : Syntax error : cannot specify '[]' in the middle of a field reference.
The list specifier ('[]') can only be used at the end of a field reference.
How to reference multiple xml tags in SELECT..WHERE.. ?
Thank you. Miron
In my data only one tag will be selected with specified criteria.
declare TEMP2 char;
set TEMP2 =
THE(select BTR.KBETR from InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[] as BTR
where InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[].(XML.attr)VTWEG ="00"
and InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[].KBETR.(XML.attr)DATBI = "99991231"
and InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[].KBETR.(XML.attr)KSCHL = "ZR00"
and coalesce(InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[].KBETR.(XML.attr)UPD_IND,' ') in (' ','U','I'));
<?xml version="1.0" encoding="UTF-8"?>
<PRICEDATA>
<COUNTRY LAND1="GB">
<SALESCHANNEL VTWEG="00">
<KBETR DATAB="19981026" DATBI="20010320" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">4356</KBETR>
<KBETR DATAB="20010321" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST" UPD_IND="U">300</KBETR>
</SALESCHANNEL>
<SALESCHANNEL VTWEG="J">
<KBETR DATAB="20010307" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
</SALESCHANNEL>
</COUNTRY>
</PRICEDATA> |
|
| Back to top |
|
 |
| kirani |
| Posted: Mon Jul 15, 2002 8:48 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Try this code,
| Code: |
set TEMP2 =
THE(select BTR.KBETR from InputRoot.XML.PRICEDATA.COUNTRY.SALESCHANNEL[] as BTR
where BTR.(XML.attr)VTWEG ="00"
and BTR.KBETR.(XML.attr)DATBI = "99991231"
and BTR.KBETR.(XML.attr)KSCHL = "ZR00"
and coalesce(BTR.KBETR.(XML.attr)UPD_IND,' ') in (' ','U','I'));
|
_________________
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 |
|
|
| miron |
| Posted: Mon Jul 15, 2002 9:12 am Post subject: |
|
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hello Kiran,
I already tried your suggestion but it also does not work. This is an error:
BIP2432E: (14, 35) : The correlation name '00' is not valid. Those in scope are: Environment, InputLocalEnvironment, OutputLocalEnvironment, InputRoot, InputBody, InputProperties, OutputRoot, InputExceptionList, OutputExceptionList, InputDestinationList, OutputDestinationList, I, TEMP1, TEMP2, BTR.
The first element of a field reference must be a valid correlation name, from those in scope. This message may sometimes be due to an incorrectly formed or spelled expression which is not intended to be a field reference being parsed as if it were a field reference because the parser does not recognize it.
It looks like SELECT..WHERE.. behaves differently when used with database and with xml data.
Thanks.Miron |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Jul 15, 2002 10:14 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Miron,
| Quote: |
where BTR.(XML.attr)VTWEG = "00"
and BTR.KBETR.(XML.attr)DATBI = "99991231"
and BTR.KBETR.(XML.attr)KSCHL = "ZR00"
|
Oops .. You need to have ' instead of " on RHS!
Since you have multiple occurrences of <KBETR> within <SALESCHANNEL>, you need to have some kind of index within the Where clause.
_________________
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
