Re: SQL rows to columns

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.