Re: Sql Case question

The case cannot communicate a list to the group by. You need a case
statement for each field that is not common between the two conditions.
Now, I see the size of the list is different between the two conditions.
You'll have to use aggregate functions around those fields, in the select
statement, such as max() to prevent errors on invalid fields in the select
list.


___________________________________
Darren Strong
Dekko





From: Jack Tucky <jacktucky@xxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>
Date: 07/31/2017 09:46 AM
Subject: Sql Case question
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



I want to use CASE on my order by and group by. This syntax isn't
working.

group by
case when :S1SKU = 'Y' then
LM.TYP, LS.SHP, LS.SEA, LS.STY, LS.CLR, LS.FAB, LS.MOD,
LS.MSZ, LS.SIZ, LS.AUX1, LS.AUX2, LS.HST, LS.STS,
LS.HRE, LS.PST, LS.RID, LS.PLT, LX.BLD, LX.SEC,
LX.SLT, LX.AIS, LX.LVL
ELSE
LM.TYP, LS.SHP, LS.SEA, LS.STY, LS.CLR, LS.FAB, LS.MOD,
LS.MSZ, LS.SIZ, LS.AUX1, LS.AUX2, LS.HST, LS.STS,
LS.HRE, LS.PST, LS.RID, LS.PLT, LX.BLD, LM.SRT
END


I've seen some examples where you specify "Case" for each example instead
of else, and also the END needs to be after the first field LM.TYP but
neither worked form.

Thanks for any help.

Art
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD