Thanks for your reply Niels
Let me look into join lateral – not too sure about the “fetch first 1 rows only” though as there could be up to 20 payments (or more) per entity
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
From: Niels Liisberg [mailto:nli@xxxxxxxxxxxxxxxxx]
Sent: Tuesday, September 22, 2020 4:34 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: [EXTERNAL] Re: Need help on capturing data in a query
Use “join lateral “ that allows you to specify “fetch first 1 rows only”
tir. 22. sep. 2020 kl. 22.28 skrev Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>:
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><mailto: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<mailto: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<mailto: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<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com<
http://midrange.com> by shopping at amazon.com<
http://amazon.com> with our affiliate link:
https://amazon.midrange.com
midrange.com
