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



Hi Kirk,

This looks to be a de-normalization task.

If I understand this task correctly, the SQL to generate/update the
de-normalized row should look like the UPDATE below:

create table QTEMP.TEST ( P, Q, R, S, T, U ) as (
values ( 'P', '', '', '', '', '' )
,( 'Q', '', '', '', '', '' )
,( 'R', '', '', '', '', '' )
,( 'S', '', '', '', '', '' )
,( 'T', '', '', '', '', '' )
,( 'U', '', '', '', '', '' )
) with data
;

update QTEMP.TEST T
set ( Q, R, S, T, U ) = (
select
max( case when rrn( S ) = 2 then S.P else '' end ) as Q
,max( case when rrn( S ) = 3 then S.P else '' end ) as R
,max( case when rrn( S ) = 4 then S.P else '' end ) as S
,max( case when rrn( S ) = 5 then S.P else '' end ) as T
,max( case when rrn( S ) = 6 then S.P else '' end ) as U
from QTEMP.TEST S
where rrn( S ) >= 2
group by 1 )
where rrn( T ) = 1
;

Mike

date: Tue, 25 Jul 2017 07:35:17 -0700
from: Kirk Goins <kirkgoins@xxxxxxxxx>
subject: Re: A little SQL help please

The file can be from 1 to 6 records. The file is created using the CPYF cmd
getting all records where field A is equal to xxxxxxxxxx. The source data
is already sorted so the fields I am dealing with are in the desired order.

Thanks

On Tue, Jul 25, 2017 at 5:51 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi Kirk

First question = what makes a record the first record? Is this by RRN? Or
by some index?

Next question = do you have RESUEDLT turned on for the file?

Cheers
Vern

On 7/24/2017 5:35 PM, Kirk Goins wrote:

If I knew RPG I know this would be simple...
A program is populating column P of a table. Columns Q, R, S, T and U
are
empty.
I want to use SQL to place the value of the 2nd record of Column P into
Q
of the 1st record
Then Repeat putting the value 3rd record Column P into R etc.

Original File Desired Results I would even settle for
P Q R S T U P Q R S T U P Q R S T U
a a b c d e f a b c d e f
b a b c
d e
f
c a b c d
e f
d a b c
d e
f
e a b c d
e f
f a b c d
e
f

Thoughts and suggestions

Thanks


Kirk




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.