× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. September 2020

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

I think what I am looking for is
ROW_NUMBER and PARTITION BY
I'll look into it more in the morning



Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone


-------- Original message --------
From: Alan Shore <ashore@xxxxxxxx>
Date: 9/22/20 20:51 (GMT-05:00)
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: Need help on capturing data in a query

Thanks for your reply Roger
Charles had already mentioned about LISTAGG and I had already seen what results were given
I am redirecting my thoughts and trying to see how to create a sequence within each key
A sequence that starts from 1 for each change of key



Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone


-------- Original message --------
From: Roger Harman <roger.harman@xxxxxxxxxxx>
Date: 9/22/20 20:24 (GMT-05:00)
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: Need help on capturing data in a query

ListAgg is a great function. I use it regularly.

BUT... The result is a single column and you said each payment needs to be reported separately which I inferred to equate to multiple columns..

My typical use is to provide concatenated comment text or a list of email addresses per customer to a user.

Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power





-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Alan Shore via MIDRANGE-L
Sent: Tuesday, September 22, 2020 1:46 PM
To: Midrange Systems Technical Discussion
Cc: Alan Shore
Subject: RE: [EXTERNAL] Re: Need help on capturing data in a query

Thanks for the reply Charles
Let me look at LISTAGG

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 Charles Wilt
Sent: Tuesday, September 22, 2020 4:44 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] 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

--
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
--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.