| Author |
Message
|
| wmbv7newbie |
 Posted: Wed Jul 16, 2014 1:33 am Post subject: WMB V8 - Mapping Node - Custom ESQL Input Tree |
 |
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Hi,
I am using custom esql for a few fields in the mapping node. The requirement is quite simple. I have an array as an input field. I have to loop through all the occurrences and if one of them [3rd occurrence] is not null, I have to pass that value to the output, else another [2nd occurrence].
I have attached a 'Custom Esql' Transform to the input array element which maps to the output element.
The input looks like -
| Code: |
<Shipping>
<ShippingAmount>
<AmountType>Original</AmountType>
<Amount>8.82</Amount>
</ShippingAmount>
<ShippingAmount>
<AmountType>Standard</AmountType>
<Amount>8.92</Amount>
</ShippingAmount>
<ShippingAmount>
<AmountType>Total</AmountType>
<Amount>1234</Amount>
</ShippingAmount>
</Shipping>
|
However, the below code is not working -
| Code: |
CREATE FUNCTION mapShipping (IN amountIn REFERENCE)RETURNS DECIMAL
BEGIN
DECLARE amount DECIMAL NULL;
DECLARE child DECIMAL;
SET child = CARDINALITY(amountIn);
--If 3rd child i.e. Total present, map that, else map Standard i.e. 2d child
--IF amountIn[3].Amount[1] <> 0 THEN -- this not working
IF amountIn.ShippingAmount[3].Amount[1] <> 0 THEN -- this is not working
SET amount = amountIn.ShippingAmount[3].Amount[1];
ELSEIF amountIn.ShippingAmount[2].Amount[1] IS NOT NULL THEN
SET amount = amountIn.ShippingAmount[2].Amount[1];
END IF;
RETURN amountIn.Amount[1];
END;
|
I know this looks quite stupid, but I would appreciate any leads. I am going to try using the trace node to troubleshoot though. |
|
| Back to top |
|
 |
| Simbu |
| Posted: Wed Jul 16, 2014 3:01 am Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
Infocenter says,
| Quote: |
| The input and return datatypes must be simple scalars; ESQL reference datatypes are not supported |
you may have to try some other options. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Wed Jul 16, 2014 6:19 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
| No Arrays can ne used in input then...that's unbelievable! |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 16, 2014 6:33 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmbv7newbie wrote: |
| No Arrays can ne used in input then...that's unbelievable! |
And yet true, like so many things in life.
Stepping back from the technology to the requirement, you're making an assumption (and presumably one grounded in knowledge of the system) that the AmountType with a value of Total is the 3rd element. There's nothing XML wise I can see that would prevent that being the 2nd element and the Standard type the thrid. Clearly it's some kind of convention that AmountType is presented in that sequence, and that the Standard type is present, but there's no enforcement. It'll only take one new program with a bug (or who doesn't know the rules) or a vendor to break your logic.
You'd be much better advised to SELECT the AmountType elements with Standard and Total as values, and react accordingly if you can't find one or the other. This also means you're convieniently passing 2 scalars to your procedure.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Wed Jul 16, 2014 6:49 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Thanks for your reply.
| Quote: |
| you're making an assumption (and presumably one grounded in knowledge of the system) that the AmountType with a value of Total is the 3rd element. There's nothing XML wise I can see that would prevent that being the 2nd element and the Standard type the thrid. Clearly it's some kind of convention that AmountType is presented in that sequence, and that the Standard type is present, but there's no enforcement. |
Yes. We have our canonical structure. So I am pretty sure where the Standard type is and where the Total type is. The only check has to be whether that type has a value in the Amount field.
| Quote: |
| You'd be much better advised to SELECT the AmountType elements with Standard and Total as values, and react accordingly if you can't find one or the other. |
This is a better way I guess. Let me try this though I am still a novice with XPath queries.
Thanks again! |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 16, 2014 6:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| wmbv7newbie wrote: |
| Quote: |
| You'd be much better advised to SELECT the AmountType elements with Standard and Total as values, and react accordingly if you can't find one or the other. |
This is a better way I guess. Let me try this though I am still a novice with XPath queries. |
I was talking about the ESQL SELECT function, which uses (as the name suggests) a database like sql rather than an XPath and is a bit easier.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Wed Jul 16, 2014 7:37 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
Oh ok. So, I went with a similar approach and used the scalars as input to the ESQL. I am taking as Input to esql, Amount value of cardinality 2 and 3 and then checking for the value. But getting below exception at the function -
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\slot1\S800_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line:INTEGER:1154
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMSLMappingNode
Name:CHARACTER:TransformCanonicalToOasis#FCMComposite_1_8
Label:CHARACTER:TransformCanonicalToOasis.Mapping
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:MbErrorHandler.java
Line:INTEGER:154
Function:CHARACTER:evaluate
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:3947
Text:CHARACTER:Caught BrokerXCIDynamicException
Insert
Type:INTEGER:5
Text:CHARACTER:{com.pearson.oas}:TransformCanonicalToOasis_mapping
Insert
Type:INTEGER:5
Text:CHARACTER:Move: let $Amount := $Body0/Order[1]/Shipping/ShippingAmount/Amount
RecoverableException
File:CHARACTER:MbErrorHandler.java
Line:INTEGER:281
Function:CHARACTER:throwableToMbException
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:3949
Text:CHARACTER:Caught BrokerXCIDynamicException
Insert
Type:INTEGER:5
Text:CHARACTER:<com.ibm.broker.plugin.MbRecoverableException class:JNI method:SqlRoutine::execute(SqlStatementResult& ,SqlReadCursor ) source:BIPmsgs key:4187 >
RecoverableException
File:CHARACTER:F:\build\slot1\S800_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:1106
Function:CHARACTER:SqlRoutine::execute(SqlStatementResult& ,SqlReadCursor )
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:4187
Text:CHARACTER:value when expecting ref
Insert
Type:INTEGER:5
Text:CHARACTER:mapShipping
Insert
Type:INTEGER:2
Text:CHARACTER:1 |
The function looks like -
| Code: |
CREATE FUNCTION mapShipping (IN stndrdAmount REFERENCE, IN totalAmount REFERENCE) RETURNS DECIMAL
BEGIN
DECLARE amount DECIMAL 1;
--If 3rd child i.e. Total prsent, map that, else map Standard i.e. 2d child
--IF amountIn[3].Amount[1] <> 0 THEN -- this not working
IF totalAmount <> 0 THEN
SET amount = totalAmount;
ELSEIF stndrdAmount <> 0 THEN
SET amount = stndrdAmount;
END IF;
RETURN amount;
END;
|
What value is this getting instead of reference. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Wed Jul 16, 2014 9:02 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
For anyone who is interested, after many issues and workarounds, the mappings are working as required. 
The problem for above issue was that I was trying to return a Decimal and pass it to Double in the calling function. [No implicit casts and Double is not a Scalar in ESQL  ]
The last problem I faced was that if I pass a null in input like -
| Code: |
<ShippingAmount>
<AmountType>Standard</AmountType>
<Amount></Amount>
</ShippingAmount> |
the flow gets stuck and I get an exception.
The workaround - I am never sending blank in Amount field. The worst case will be a 0 in place of blank.
But can anybody suggest me something else so that I don't have to put a condition on the input?
Thanks! |
|
| Back to top |
|
|
| martinb |
| Posted: Thu Jul 17, 2014 8:31 am Post subject: XPath predicates select array instance in Graphical Data Map |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Sorry to join the thread late.
I would like to propose a robust graphical data mapping solution here.
As per the comments in the thread you really should be looking to implement a solution that is designed to pick the correct "Amount" value based on value of the "AmountType" element rather than depend on a particular order of the array entries. The XML data would be valid according to it's schema regardless of the order.
As noted ESQL provides the SELECT statement for implementing this.
However there is no need to go to the complexity and overhead of calling out of the Graphical Data Map when it is itself equip with the full power of Xpath 2.0.
Specifically XPath predicates are the answer here. (I think the w3schools tutorial is great for getting upto speed on this, it really is not complex and well worth the the investment).
From the example data provided, and subsequent qualification of the need to cater for an empty field I understand the requirement to be
If there is a "ShippingAmount" instance "AmountType" set to "Total" use this instance "Amount"
Else if the "ShippingAmount" instance with "AmountType" set to "Standard" has a none empty "Amount" use it
Else set a default in "Amount"
I would implement this in a Graphical Data Map as follows:-
| Code: |
ShippingAmount --- If --- ShipCost
ShippingAmount --- Else if --- ShipCost
ShippingAmount Else --- ShipCost
|
The condition for the "If" will be a predicate to select an instance of the array in which "AmountType" is set to 'Total'. (Note that that XPath implicitly yields "true" if an expresion gives a none empty node set.)
| Code: |
$ShippingAmount[AmountType = 'Total']
|
and then the nested transform inside the "If" would be to copy the "Amount" from this instance.
| Code: |
ShippingAmount --- Custom XPath--- ShipCost
|
The "if" only tested for the presence of the instance, and passes the whole array into it's nested mapping, so rather than a simple "Move" we need to use a "Custom XPath" with the XPath predicate expression to select "Amount" from this instance we now know exists
| Code: |
$ShippingAmount1[AmountType = 'Total']/Amount
|
The condition for the "Else if" would be, assuming a schema type for Amount is xsd:decimal, a predicate to select the "standard" instance, wrapped in a cast to a string and test for not equal to the empty string
| Code: |
xs:string( $ShippingAmount3[AmountType = 'Standard']/Amount ) != ''
|
and the nested transform inside the "Else if" would again be a Custom XPath to pick the "Amount" we now know to be present
| Code: |
ShippingAmount --- Custom XPath--- ShipCost
|
with the XPath predicate expresion
| Code: |
$ShippingAmount1[AmountType = 'Standard']/Amount
|
Finally the "Else" would just have an "Assign" in it's nested mapping to set your default.
HTH
PS always remember to use content assist, ctrl-space, to create your conditions and expressions to ensure the correct syntax and variable names. |
|
| Back to top |
|
|
| wmbv7newbie |
| Posted: Sat Jul 19, 2014 9:38 am Post subject: |
|
|
Centurion
Joined: 13 May 2014
Posts: 121
|
This looks awesome! Thanks @martinb
I began with a similar idea of using the If statements to check the AmountType but my XPath queries (even when using the ctrl+space assist) were not working as expected.
Due to the time crunch I thought of going the easier (which i reluctantly chose) way.
But now that I have got a little more confidence and your detailed explanation of the solution, I am going to try this again and hope it works.
Thanks a ton! |
|
| Back to top |
|
|
|
|
