Hi Birgitta
Sorry to bother you about this - but I am getting myself well and truly lost on this.
This is a snapshot of the data.
CBCUST CBINV# CBCHK# DATE_PAID CBPAID CBDISC Row_number
22130 000036 ADJ113384030 12/10/2019 834.37 17.03 1
22130 000037 WT-112713849 12/2/2019 5,238.96 106.92 1
22130 000038 WT-112713849 12/2/2019 4,896.65 99.93 1
22130 000038 ADJ116572038 1/9/2020 15.92 0.32 2
22130 000038 ADJ ACCT 1/16/2020 85.26 0.00 3
22130 000039 WT-112561765 11/26/2019 483.10 9.86 1
22130 000047 WT-112680341 11/26/2019 138,010.14 2,816.54 1
22130 000047 WT-114103881 12/17/2019 82.50 1.68 2
22130 000047 WT-115715006 12/27/2019 501.33 10.23 3
22130 000047 ADJ116592825 1/10/2020 116.24 2.37 4
22130 000047 ADJ ACCT 7/17/2020 435.41 0.00 5
22130 000156 WT-112561765 11/26/2019 10,746.17 219.31 1
The columns CBCUST, CBINV#, CBCHK#, DATE_PAID, CBPAID, CBDISC are from my file INVXTRACT
I am trying to generate the Row_Number for the CBCUST,CBINV combination
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 Birgitta Hauser
Sent: Tuesday, September 22, 2020 11:50 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Need help on capturing data in a query
1. it is not possible to generate a varying number of columns, ... but for a predefined number of (payment) columns it would work. (If you do not need separate columns use LISTAGG) 2. Generate first a column where you generate a sequence with ROW_NUMBER 3. In the final SELECT you group your payments according the ROW_NUMBER
Something like this:
With x as (Select JoinKey1, JoinKey2, Payment, Row_Number() Over(Partition By JoinKey1, JoinKey2 Order By PaymentDate) Counter
From .)
Select a.JoinKey1, a.JoinKey2, Sum(case When Counter = 1 Then Payment Else
0 End) Payment_1,
Sum(Case When Counter = 2 Then Payment Else
0 End) Payment_2,
.
Sum(Case When Counter = n Then Payment Else
0 End) Payment_N
From YourFile a Join x on a.JoinKey1 = x.JoinKey1 and a.JoinKey2 =
x.JoinKey2
Group By a.JoinKey1, a.JoinKey2
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Dienstag, 22. September 2020 22:29
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 : 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: mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact mailto: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
midrange.com
