MQSeries.net :: View topic - Esql Select Transformation

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 » Esql Select Transformation

Goto page 1, 2 Next

Esql Select Transformation

« View previous topic :: View next topic »

Author

Message

RAN001

Posted: Tue Feb 14, 2017 2:17 pm Post subject: Esql Select Transformation

Novice

Joined: 14 Feb 2017
Posts: 11

Long time lurker, first time poster. Thank you for all of your help before with your questions, and any additional help you can give with this.

I have an XMLNSC document that looks like this:

Code:

I would like to write an Esql Select statement if possible that would transform it to this output:

Code:

Is this possible and can you please provide an example?
Thanks!

Last edited by RAN001 on Wed Feb 22, 2017 1:29 pm; edited 1 time in total

timber

Posted: Wed Feb 15, 2017 1:48 am Post subject:

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Tip: when posting code/data you should always use the [c o d e] button so that the indentation is displayed. Makes it easy for the people whose help you are requesting.

mqjeff

Posted: Wed Feb 15, 2017 5:05 am Post subject: Re: Esql Select Transformation

Grand Master

Joined: 25 Jun 2008
Posts: 17447

RAN001 wrote:

I would like to write an Esql Select statement if possible that would transform it to this output

Excellent idea! ESQL is flexible and can do all of the data transformation you need.

RAN001 wrote:

Is this possible and can you please provide an example?
Thanks!

Sure, it's possible. ESQL is Turing Complete.

Some questions:

How do you know which LineItem goes with which AssociatedLineNum ?
Do you need to do some kind of sorting arrangement?
Do you need to do some kind of Grouping?

ESQL Select can't sort or group by. You can certainly do Select .<AssociatedLineItem> as A, ... as B from .. where A.something == B.something

An example really depends on the real structure of your data, and the needs you have.

There are also, I'm sure, examples of SELECT statements in a sample.
_________________
chmod -R ugo-wx /

RAN001

Posted: Wed Feb 15, 2017 8:07 am Post subject:

Novice

Joined: 14 Feb 2017
Posts: 11

So the Input XML document looks like this:

Code:

Quote:

How do you know which LineItem goes with which AssociatedLineNum ?

The AssociatedLineNum and the LineNum are both contained under the same LineItem Tag. This is how you know they go together.

I am trying to get one output record for each AssociatedLineNum. I just don't understand how to write the statement to output the LineNum when the reference would be pointed at the AssociatedLineNum. (Essentially going back up the tree) How do you write a statement to reference a relative parent element of the child reference in esql? This is probably the crux of the issue I am having.

Thanks!

mqjeff

Posted: Wed Feb 15, 2017 8:29 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

RAN001 wrote:

I just don't understand how to write the statement to output the LineNum when the reference would be pointed at the AssociatedLineNum. (Essentially going back up the tree) How do you write a statement to reference a relative parent element of the child reference in esql?

Two options...

I would usually loop over the parents and access the children...

Or you can Move to the Parent...
https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak05090_.htm
_________________
chmod -R ugo-wx /

RAN001

Posted: Wed Feb 15, 2017 8:49 am Post subject:

Novice

Joined: 14 Feb 2017
Posts: 11

Thanks mqjeff.

I understand I could write a nested loop in esql and do this. I wanted to see if there was a esql select operation that could do the same thing. I can't find a way personally to do this. I was hoping someone else might no a way. SQL is my friend

so I try not to break into nested loops until I have to. I see this as a learning opportunity.

Thanks,

Andy

mqjeff

Posted: Wed Feb 15, 2017 9:09 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

You can do a SELECT that loops over the parent and then also asks for the child elements?

Like select A.LineItem, A.LineItem.LineNum From InputRoot.Order as A

...
_________________
chmod -R ugo-wx /

timber

Posted: Wed Feb 15, 2017 11:23 am Post subject:

Grand Master

Joined: 25 Aug 2015
Posts: 1280

I applaud your desire to avoid nested loops. However, in this case I think the most obvious and straightforward solution is to implement an algorithm that looks like this:

Code:

For each LineItem
For each AssociatedLineNum
copy the LineItem into the output tree if it does not already exist
copy this AssociatedLineNum into the output tree as its child

It is easy to write, you can add comments explaining what each part does and why, and future maintainers will thank you for not using a SELECT statement that required approximately 2000 years of combined IIB experience. And it will probably perform at least as well as the equivalent SELECT statement (assuming that SELECT could be persuaded to do this).

RADHAPAVAN

Posted: Wed Feb 15, 2017 11:37 pm Post subject: Try using this

Newbie

Joined: 10 Jan 2011
Posts: 3

Code:

CREATE FIELD OutputRoot.XMLNSC.Order;
   DECLARE outreford REFERENCE TO OutputRoot.XMLNSC.Order;
   CREATE FIELD outreford.LineItem;
   DECLARE strfcount,count INTEGER 1;
   SET strfcount = CARDINALITY(InputRoot.XMLNSC.Order.LineItem[]);
   DECLARE dtlcount , dtloutput,idx INTEGER 1;
   WHILE count<=strfcount DO
      SET dtloutput = 1;
      SET dtlcount = CARDINALITY(InputRoot.XMLNSC.Order.LineItem[count].AssociatedLineNums.AssociatedLineNum[]);
         WHILE dtloutput <= dtlcount DO
            SET outreford.LineItem[idx].LineNum = InputRoot.XMLNSC.Order.LineItem[count].LineNum;
            SET outreford.LineItem[idx].AssociatedLineNum = InputRoot.XMLNSC.Order.LineItem[count].AssociatedLineNums.AssociatedLineNum[dtloutput];
            SET idx = idx+1;
            SET dtloutput = dtloutput+1;
         END WHILE;
      SET count = count+1;
   END WHILE;

joebuckeye

Posted: Thu Feb 16, 2017 6:20 am Post subject:

Partisan

Joined: 24 Aug 2007
Posts: 364
Location: Columbus, OH

No, never use CARDINALITY and while loops to iterate, use FOR loops instead.

You will need to add a CREATE LASTCHILD line but the code would be much, much cleaner.

Vitor

Posted: Thu Feb 16, 2017 9:38 am Post subject:

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

joebuckeye wrote:

No, never use CARDINALITY and while loops to iterate, use FOR loops instead.

Massive performance hit.
_________________
Honesty is the best policy.
Insanity is the best defence.

RAN001

Posted: Thu Feb 16, 2017 12:13 pm Post subject:

Novice

Joined: 14 Feb 2017
Posts: 11

So first let me say I am grateful to all who have contributed so far, and I even agree that a select may not be the best way to go. And the most performant nested loop solution is interesting as well, both from a speed and memory usage solution. However I am still curious if this can be done with a ESQL Select statement as opposed to a loop.

Thanks,

Andy

rekarm01

Posted: Mon Feb 20, 2017 12:20 pm Post subject: Re: Esql Select Transformation

Grand Master

Joined: 25 Jun 2008
Posts: 1415

Another tip: to add [code] tags to previously submitted posts, use the [Edit] button in the upper right corner of the post.

joebuckeye wrote:

No, never use CARDINALITY and while loops to iterate, use FOR loops instead.

FOR loops, or other loops. ESQL offers so many to choose from.

Vitor wrote:

Massive performance hit.

Sometimes massive. Sometimes not.

RAN001 wrote:

However I am still curious if this can be done with a ESQL Select statement as opposed to a loop.

This seems to work for the given input, but it uses two SELECT calls:

Code:

DECLARE R ROW;

SET R.LineItem[] = SELECT L FROM InputRoot.XMLNSC.Order.LineItem[] AS L
WHERE EXISTS(L.AssociatedLineNums.AssociatedLineNum[]);

SET OutputRoot.XMLNSC.Output.LineItem[] = SELECT L.LineNum AS LineNum, A AS AssociatedLineNum
FROM R.LineItem[] AS L, L.AssociatedLineNums.AssociatedLineNum[] AS A;

Whether that is a technically legal use of lists in a FROM clause is another matter. Use at your own risk.

mgk

Posted: Tue Feb 21, 2017 3:45 am Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1638

Quote:

Whether that is a technically legal use of lists in a FROM clause is another matter

Yes, this is legal, just not well documented

_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.

RAN001

Posted: Wed Feb 22, 2017 1:39 pm Post subject:

Novice

Joined: 14 Feb 2017
Posts: 11

Thanks All especially rekarm01 and timber. I learned something which is always the point of these groups to me. I really do appreciate the help.

Display posts from previous:

Goto page 1, 2 Next

Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Esql Select Transformation

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