Re: Need help on capturing data in a query

[Raul Alberto Jager Weiler <raul.jager@xxxxxxxxx>]

This job seems to be easier using RPG

On Tue, Sep 22, 2020 at 4:54 PM Karl Haggart <
karl.haggart@xxxxxxxxxxxxxxxxxx> wrote:

> Hi Alan,
>
> Does this help you?
>
>
> https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyrecursivequeries.htm
>
> I think it may have the answer you are looking for, and has a fairly easy
> to follow example too.
>
> Cheers,
> Karl.
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Alan Shore via MIDRANGE-L
> Sent: Tuesday, September 22, 2020 1:29 PM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Cc: Alan Shore <ashore@xxxxxxxx>
> Subject: Need help on capturing data in a query
>
> 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
> --
> 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