RE: [EXTERNAL] Re: Need help on capturing data in a query

Thanks for your reply Carel
Each payment has to be reported separately
Payment date, payment amount, discount amount, payment type (Check, cash, wire transfer)

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

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Carel
Sent: Tuesday, September 22, 2020 4:32 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Need help on capturing data in a query

Why not GROUP those records with SUM?


Op 22-9-2020 om 22:28 schreef Alan Shore via MIDRANGE-L:

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