Re: Need help on capturing data in a query

Yeah, well good luck...

The problem is that Db2 for IBM i lacks a built in "Pivot table" function.

You can manually pivot the data, but you have to hard code the number of
pivot values...assuming that's a reasonable number like "no entity has more
than 5 payments" it's not so bad. But if there's 100 payments...

This kind of data exchange is a perfect fit for XML or JSON.

I'd try real hard to see about using one of those.

The only other thing that might help you, would be this LISTAGG()
function. It would let you consolidate the multiple payment information
into a single string.

Charles



On Tue, Sep 22, 2020 at 2:28 PM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi everyone
We are on v7r3

I hope I can explain this

I have a query that creates an extract - one record per entity
I need to capture extra data from another file, but this other file can
have multiple records per entity

This extra file contains payment information

Instead of creating an extract with multiple records per entity, I have
been asked to leave it at one record per entity
The only way that I can see this working is to add each piece of data to
the end of the one record

For example
One entity has 2 payments, therefore create 2 new fields on the one entity
Payment_1, Payment_2
Another entity has 5 payments, therefore create the 5 fields
Payment_1, Payment_2, Payment_3, Payment_4, Payment_5
The next entity has 4 payments, therefore create the 4 fields
Payment_1, Payment_2, Payment_3, Payment_4

Hope this makes SOME sense

My question is - how can I do this?

I thought of creating a CTE of
Entity, Payment information, Sequence number
Where the sequence number is the chronological instance of the payment for
that entity
Question is - how to create a sequence number
Then based upon the sequence number, put the payment information into
Payment_1 if sequence is 1, Payment_2 if sequence is 2, Payment_3 if
sequence is 3, Payment_4 if sequence is 4, Payment_5 if sequence is 5
Etc.

Yeesh
I just read this through god knows how many times
Changed the explanation, god know how many times
Hope this makes sense

As always - all answers gratefully accepted



Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com