× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. July 2017

Re: Sql Case question

Thanks again Darren.

I am also changing the select to match the group by. Based on the user
selection, I may want to change these fields LX.SEC,LX.SLT, LX.AIS, LX.LVL
to LX.SRT. So I would replace those fields in the select as well.

Here is the full statement

select 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,
cast(count(LS.CTN) as Decimal(6,0)) as wk1Ctn ,
cast(sum(LS.QTY) as Decimal(6,0)) as wk1Pcs
from locsku00 ls

inner join locsum00 lx
on LS.SHP = LX.SHP
and LS.SEA = LX.SEA
and LS.STY = LX.STY
and LS.CLR = LX.CLR
and LS.FAB = LX.FAB
and LS.MOD = LX.MOD
and LS.MSZ = LX.MSZ
and LS.SIZ = LX.SIZ
and LS.AUX1 = LX.AUX1
and LS.AUX2 = LX.AUX2
and LS.HST = LX.HST
and LS.STS = LX.STS
and LS.HRE = LX.HRE
and LS.PST = LX.PST
and LS.RID = LX.RID
and LS.PLT = LX.PLT

inner join skusku00 ss
on LS.SHP = ss.SHP
and LS.SEA = ss.SEA
and LS.STY = ss.STY
and LS.CLR = ss.CLR
and LS.FAB = ss.FAB
and LS.MOD = ss.MOD
and LS.MSZ = ss.MSZ
and LS.SIZ = ss.SIZ

inner join locmst00 lm
on Lx.BLD = lm.BLD
and Lx.SEC = lm.SEC
and Lx.AIS = lm.AIS
and Lx.SLT = lm.SLT
and Lx.LVL = lm.LVL

where ls.shp= :wkShp#
and (:s1bld =' ' or :s1bld = lx.bld)
and (:s1sec =' ' or :s1sec = lx.sec)
and (:s1sty =' ' or :s1sty = lx.sty)
and (ls.ctn > 0)
and (ls.qty > 0)

group by 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

order by 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;



On Mon, Jul 31, 2017 at 1:16 PM, Darren Strong <darren@xxxxxxxxx> wrote:

PREPARE is not going to help with the differing field count in the GROUP
BY. The select statement will fail if you select on fields that are not
in the group by, or prefixed by an aggregate function. I hesitate to just
give you the answer in the GROUP BY, because I suspect you may also have
an issue in the SELECT section of your statement so I don't really no how
to fix the GROUP BY code. Chances are, you can make the two GROUP BY
sections much more similar than they currently are.


___________________________________
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 12:01 PM
Subject: Re: Sql Case question
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



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

--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.