MQSeries.net :: View topic - Using SELECT statment in input message

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Using SELECT statment in input message

Using SELECT statment in input message

« View previous topic :: View next topic »

Author

Message

Bravo

Posted: Mon Aug 13, 2007 1:31 pm Post subject: Using SELECT statment in input message

Centurion

Joined: 03 Oct 2005
Posts: 146

Hi All,

I'm using SELECT statment to convert from input MRM(XML) message to generic XML in WBIMB,

Here is the input,

Code:

<?xml version="1.0" encoding="UTF-8"?>
<Q1:temp_zsal_id01_real_bbcb_rout.xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/temp_zsal_id01_real_bbcb_rout" version="3.0.0" verb="Create" locale="en_US" delta="false">
<Q4:temp_gt_suplcosts xmlns:Q4="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/temp_gt_suplcosts" version="3.0.0" verb="" locale="en_US" delta="false">
<Q4:V_MATNR>17-12126-000</Q4:V_MATNR>
<Q4:V_LIFNR>C231</Q4:V_LIFNR>
<Q4:V_EKORG>FL01</Q4:V_EKORG>
<Q4:V_KBETR>.15</Q4:V_KBETR>
<Q4:V_KONWA>USD</Q4:V_KONWA>
<Q4:V_DATBI>99991231</Q4:V_DATBI>
<Q4:V_DATAB>20070101</Q4:V_DATAB>
</Q4:temp_gt_suplcosts>
</Q1:temp_gt_suplcosts>
<Q3:temp_gt_suppliers xmlns:Q3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/temp_gt_suppliers" version="3.0.0" verb="" locale="en_US" delta="false">
<Q3:V_MATNR>17-12126-000</Q3:V_MATNR>
<Q3:V_EKORG>FL01</Q3:V_EKORG>
<Q3:V_LIFNR>C231</Q3:V_LIFNR>
<Q3:V_NAME1>FLEXALLOY INC</Q3:V_NAME1>
</Q3:temp_gt_suppliers>
</Q1:temp_gt_suppliers>

Using the ESQL code, got the expected Output when temp_gt_suppliers and temp_gt_suplcosts are avaiable,

ESQL CODE

Code:

SET OutputRoot.XML.Response.PartInfo.PrimarySuppliers.PrimarySupplier[] = (
      SELECT PS.splr:V_LIFNR AS PrimarySupplierID,
            PS.splr:V_NAME1 AS SupplierName,
            PS.splr:V_EKORG AS PurchasingOrg,
            T.costs:V_EKORG AS SupplierCosts.SupplierCost.PurchasingOrg,
            T.costs:V_KBETR AS SupplierCosts.SupplierCost.NetPriceSupplierCost,
            T.costs:V_KONWA AS SupplierCosts.SupplierCost.CurrencyKey,
            SUBSTRING(T.costs:V_DATBI FROM 1 FOR 4) || '-' ||
            SUBSTRING(T.costs:V_DATBI FROM 5 FOR 2) || '-' ||
            SUBSTRING(T.costs:V_DATBI FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidToDate,
            SUBSTRING(T.costs:V_DATAB FROM 1 FOR 4) || '-' ||
            SUBSTRING(T.costs:V_DATAB FROM 5 FOR 2) || '-' ||
            SUBSTRING(T.costs:V_DATAB FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidStartDate

      FROM InputRoot.MRM.part:temp_gt_suppliers.splr:temp_gt_suppliers[] AS PS
         , InputRoot.MRM.part:temp_gt_suplcosts.costs:temp_gt_suplcosts[] AS T
      WHERE T.costs:V_EKORG = PS.splr:V_EKORG
         and T.costs:V_LIFNR = PS.splr:V_LIFNR);

It works perfectly fine and getting expected output as

Code:

<PrimarySuppliers>
<PrimarySupplier>
<PrimarySupplierID>C231</PrimarySupplierID>
<SupplierName>FLEXALLOY INC</SupplierName>
<PurchasingOrg>FL01</PurchasingOrg>
<SupplierCosts>
<SupplierCost>
<PurchasingOrg>FL01</PurchasingOrg>
<NetPriceSupplierCost>.15</NetPriceSupplierCost>
<CurrencyKey>USD</CurrencyKey>
<ValidToDate>9999-12-31</ValidToDate>
<ValidStartDate>2007-01-01</ValidStartDate>
</SupplierCost>
</SupplierCosts>
</PrimarySupplier>
</PrimarySuppliers>

Now there is a slight change in the requirement with an additional scenario, when messages comes with temp_gt_suppliers data and no data for temp_gt_suplcosts (i.e.),

Code:

Then the expected should be,

Code:

<PrimarySuppliers>
<PrimarySupplier>
<PrimarySupplierID>C231</PrimarySupplierID>
<SupplierName>FLEXALLOY INC</SupplierName>
<PurchasingOrg>FL01</PurchasingOrg>
</PrimarySupplier>
</PrimarySuppliers>

I tried to modify the above esql code in many ways, SELECT with condition and with no codition but I'm not getting the expected output.

Any suggestion!!!
_________________
Bravo

jefflowrey

Posted: Mon Aug 13, 2007 1:47 pm Post subject:

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

What's the error you get, or what's the output you get, when you run the SELECT unchanged against the new input message?

What are the ways you've tried to change the SELECT?
_________________
I am *not* the model of the modern major general.

Bravo

Posted: Mon Aug 13, 2007 5:19 pm Post subject:

Centurion

Joined: 03 Oct 2005
Posts: 146

I'm not getting any errors and I tried in following ways,

1

Code:

SET OutputRoot.XML.Response.PartInfo.PrimarySuppliers.PrimarySupplier[] = (
SELECT PS.splr:V_LIFNR AS PrimarySupplierID,
PS.splr:V_NAME1 AS SupplierName,
PS.splr:V_EKORG AS PurchasingOrg,
T.costs:V_EKORG AS SupplierCosts.SupplierCost.PurchasingOrg,
T.costs:V_KBETR AS SupplierCosts.SupplierCost.NetPriceSupplierCost,
T.costs:V_KONWA AS SupplierCosts.SupplierCost.CurrencyKey,
SUBSTRING(T.costs:V_DATBI FROM 1 FOR 4) || '-' ||
SUBSTRING(T.costs:V_DATBI FROM 5 FOR 2) || '-' ||
SUBSTRING(T.costs:V_DATBI FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidToDate,
SUBSTRING(T.costs:V_DATAB FROM 1 FOR 4) || '-' ||
SUBSTRING(T.costs:V_DATAB FROM 5 FOR 2) || '-' ||
SUBSTRING(T.costs:V_DATAB FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidStartDate

FROM InputRoot.MRM.part:temp_gt_suppliers.splr:temp_gt_suppliers[] AS PS
, InputRoot.MRM.part:temp_gt_suplcosts.costs:temp_gt_suplcosts[] AS T );

Code:

SET OutputRoot.XML.Response.PartInfo.PrimarySuppliers.PrimarySupplier[] = (
SELECT PS.splr:V_LIFNR AS PrimarySupplierID,
PS.splr:V_NAME1 AS SupplierName,
PS.splr:V_EKORG AS PurchasingOrg,
T.costs:V_EKORG AS SupplierCosts.SupplierCost.PurchasingOrg,
T.costs:V_KBETR AS SupplierCosts.SupplierCost.NetPriceSupplierCost,
T.costs:V_KONWA AS SupplierCosts.SupplierCost.CurrencyKey,
SUBSTRING(T.costs:V_DATBI FROM 1 FOR 4) || '-' ||
SUBSTRING(T.costs:V_DATBI FROM 5 FOR 2) || '-' ||
SUBSTRING(T.costs:V_DATBI FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidToDate,
SUBSTRING(T.costs:V_DATAB FROM 1 FOR 4) || '-' ||
SUBSTRING(T.costs:V_DATAB FROM 5 FOR 2) || '-' ||
SUBSTRING(T.costs:V_DATAB FROM 7 FOR 2) AS SupplierCosts.SupplierCost.ValidStartDate

FROM InputRoot.MRM.part:temp_gt_suppliers.splr:temp_gt_suppliers[] AS PS
, InputRoot.MRM.part:temp_gt_suplcosts.costs:temp_gt_suplcosts[] AS T
WHERE (T.costs:V_EKORG = PS.splr:V_EKORG
and T.costs:V_LIFNR = PS.splr:V_LIFNR) OR
(PS.splr:V_EKORG > ' '));;

I really do not understand why below XML is not created using the SELECT statment when there is no SupplierCost information. All I'm getting is the root tag.

Code:

<PrimarySuppliers>
<PrimarySupplier>
<PrimarySupplierID>C231</PrimarySupplierID>
<SupplierName>FLEXALLOY INC</SupplierName>
<PurchasingOrg>FL01</PurchasingOrg>
</PrimarySupplier>
</PrimarySuppliers>

_________________
Bravo

elvis_gn

Posted: Mon Aug 13, 2007 9:33 pm Post subject:

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi Bravo,

When the section temp_gt_suplcosts does not exist, the WHERE conditions will fail, hence no data will be returned.

In your last post, the 1st query pasted should have returned atleast something, as it does not have a WHERE clause.

I understand that when both sections are available, you do a compare of few fields and select a particular section....Can you tell us what your business requirement is when either of the 2 sections are not available ? Which section are you supposed to select in that case ?

Regards.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Using SELECT statment in input message

Jump to:

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

Protected by Anti-Spam ACP