Re: SQL rows to columns

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Subselects are usually not ideal...

Just use a join...

select odcom#, odord#, odcmt#,
coalesce(n1.uccmt, '') concat  coalesce(n2.uccmt, '')
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'

Subselects have their place, but I've find they tend to be misused by
RPG'ers.
Even did a presentation about it a few years ago..
https://www.linkedin.com/posts/charles-wilt-2a4a303_sql-for-the-rpg-developer-activity-6641756015354355712-woZR?utm_source=share&utm_medium=member_desktop

HTH,
Charles


On Tue, Mar 12, 2024 at 12:46 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> Wondering if there a more "elegant" solution to my query?
>
> I have an order lines table (OEORDLOD) and a notes table (NOTEPDUC).
> For each row in the order lines table, there can be up to two rows in the
> notes table (sequence 1 and sequence 2).
> The key is ODCMT# for order lines and UCNTK for Notes.
>
> I'm trying to create one row that includes the comments in two columns
>
> My current SQL statement looks like this:
>
> select odcom#, odord#, odcmt#,
> coalesce((select uccmt from notepduc
>                     where ucnott = 'C' and ucntk = odcmt# and
>                           ucsq3# = 1),'') as comment1,
>                    coalesce((select uccmt from notepduc
>                     where ucnott = 'C' and ucntk = odcmt# and
>                           ucsq3# = 2),'') as commen2
> from oeordlod
> where odord# = '10574107'
>
> I have a similar scenario with the Order Header, but it can have up to 5
> notes lines.
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.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.