Re: SQL rows to columns

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You're going to want an index on (ucntk, ucnott, ucsq3) for NOTEPDUC if you
don't already have it...

Charles


On Wed, Mar 13, 2024 at 10:59 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

> But having said that...
>
> The "pivot" table via CTE is probably a better choice now that I think
> about it.
>
> What indexes does VE suggest you need?  ~50ms doesn't seem to bad for 12M
> records, depending on your hardware.
>
> Charles
>
> On Wed, Mar 13, 2024 at 10:48 AM Charles Wilt <charles.wilt@xxxxxxxxx>
> wrote:
>
> > If you want two columns, just remove the CONCAT
> >
> > Just use a join...
> >
> > select odcom#, odord#, odcmt#,
> > coalesce(n1.uccmt, '') as comment1,
> > coalesce(n2.uccmt, '') as comment2
> > from oeordlod
> >   left join notepduc n1
> >     on n1.ucnott = 'C'
> >        and n1.ucntk = odcmt#
> >        and n1.ucsq3# = 1
> >   left join notepduc n2
> >     on n2.ucnott = 'C'
> >        and n2.ucntk = odcmt#
> >        and n2.ucsq3# = 1
> > where odord# = '10574107'
> >
> > Charles
> >
> > On Wed, Mar 13, 2024 at 9:27 AM Greg Wilburn <
> > gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >
> > > Thanks all...
> > >
> > > I need this in two columns (eventually maybe up to 5 columns)... so
> > > listagg doesn't seem to do it.  Charles Join example also creates a single
> > > column.
> > >
> > > I used Birgitta's example - this returns the desired output, but took 60
> > > seconds.  My sub-select took 31 milliseconds.
> > > The NOTEPDUC file is large file (12m records).
> > >
> > > Run SQL Scripts Explain does not recommend any additional indexes.  The
> > > aggregation and table probe were the big winners.
> > >
> > > So I'm wondering if the sub-select is the best approach?
> > >
> > > Thx
> > >
> > >
> > > -----Original Message-----
> > > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> > > Birgitta Hauser
> > > Sent: Tuesday, March 12, 2024 4:48 PM
> > > To: 'Midrange Systems Technical Discussion' <
> > > midrange-l@xxxxxxxxxxxxxxxxxx>
> > > Subject: RE: SQL rows to columns
> > >
> > > You may try something like this (it is at least one Sub-Select less)
> > >
> > > With x as (Select Ucntk, Min(Case When UCq3# = 1 Then UCCMT End)
> > > Comment1,
> > >                          Min(Case When UCq3# = 2 Then UCCMT End) Comment2
> > >              from Notepduc
> > >              Where     UCNOTT = 'C'
> > >                    and UCSQ3 in (1, 2)
> > >              Group By UCNTK
> > > Select Odcom#, Odord#, Odcmt#,
> > >        Coalesce(Comment1, '') Comment1, Coalesce(Comment2m '') Comment2
> > >   from Oeorlod Left join x on ucntk = Odcmt#
> > >
> > > Mit freundlichen Grüßen / Best regards
> > >
> > > Birgitta Hauser
> > > Modernization - Education - Consulting on IBM i Database and Software
> > > Architect IBM Champion since 2020
> > >
> > > --
> > > 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.