Re: Sql Case question

[Jack Tucky <jacktucky@xxxxxxxxx>]

Thank you.  Can I do a CASE on the group by too?  It's looking like it
might be easier to do a PREPARE.

On Mon, Jul 31, 2017 at 10:41 AM, Darren Strong <darren@xxxxxxxxx> wrote:

> 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