Order By with Case

[jamief]

Re: Order By with Case

Can you put together a tiny little example of how to use a parameter file?

I would love to see it in action!


Happy Friday have a great weekend
Jamie

[kitvb1]

Re: Order By with Case

I'll get back to you on that - just give me a while please.

[pramendra]

Re: Order By with Case

me also waiting for example Kit...

Happy Weekend


Pramendra Pandeya

[FaStOnE]

Re: Order By with Case

*Tap* *Tap* *Tap* *Tap* *Tap* *Tap*

Sheesh ... what's taking so long?!?

[kitvb1]

Re: Order By with Case

hahaha .... I was actually thinking about a month
for a few reasons (here's my excuse(s) and I'm sticking to them )

1. I'm trying to swap the primary language on the new v610 box... @#%@#%@#%@#% I don't understand anything on it ... I think it's all in the language of as400pro's g/f.
2. I'm bogged down trying to get the next release of XrefIT and ChangefIT (some nice new features in both coming) and the first release of DeskfIT done.
3. Being pulling 15 -20 hrs/day getting the last release out so I'm trying to take it easy for a week or two and get the design and planning done.
4. I'm starting to pack to leave the country for a while ... gotta test my brand new German passport out. I became a naturalized citizen last month as there's the vote next month (now there a contradiction in terms - me natural )

[kitvb1]

Re: Order By with Case

Ok.. so I took a little time out to do some testing.

1. Try as I might, I cannot get it to find a record using static sql, i.e. just adding - " ORDER BY :dSrtflds " at the end of the declare. SQLCOD = 100 always. (I am sure Biggie can come up with some of her magic.) I tried it for about 3 hours and no go. I must admit 'cos it's not that important to me as I don't ever
use this sort selection technique. I use the field ranking method as described in the article.

2. Dynamic SQL is just boring - that took 5 mins to change the declare, define a field or two, do a prepare and Bob's-your-uncle.

IMO, if you have no obstacle against using a dynamic SQL statement, this almost makes the "Order by case when..." redundant. The reason for stating this is that I believe in soft-coding where possible. With this technique, one can add/change as many different order by's in many programs without having to change the source code.

Here's some screenshots.

If you want to see some code... hurry and ask 'cos I will deleting it soon.

[B.Hauser]

Re: Order By with Case

1. Try as I might, I cannot get it to find a record using static sql, i.e. just adding - " ORDER BY :dSrtflds " at the end of the declare. SQLCOD = 100 always. (I am sure Biggie can come up with some of her magic.) I

No I don't, because it is not possible in this way with static SQL. Static SQL cannot handle host variables with the field name within the order by clause.

Birgitta

[kitvb1]

Re: Order By with Case

Stupid SQL

[B.Hauser]

Re: Order By with Case

Why is it stupid, can you do anything comparable with native I/O?

Birgitta

[kitvb1]

Re: Order By with Case

Thinking about it more, leaving away the FORPGM field makes it even more useable, i.e. same SRTFLDS can be used for any program, e.g. enquiry & report pgms.

Chatting to Birgitta yesterday, she came up with a very good way to access this (only one read for all programs), but I'll leave her to explain it (if she wants) as it's her idea.

[pramendra]

Re: Order By with Case

I have created subfile example through dynamic cursor...

Subfile sort through SQL..

Used order by clause...

Jamii I would like to have you comment to make programe better..

Pramendra Pandeya

[B.Hauser]

Re: Order By with Case

Jamii I would like to have you comment to make programe better..

Even though I'm not Jamie ...
if you are already using SQL, remove the subroutine $DAY and calculate the day name as follows (assuming you are working with an English system and want the day names in English):

Exec SQL   Set :#Day = DayName(Current_Date)
End-Exec 

Birgitta

[pramendra]

Re: Order By with Case

Thanks!!!

Your comments are always invaluable..


Pramendra Pandeya

[kitvb1]

Re: Order By with Case

Came across this yesterday and is what I was referring to in the article about maintainability and "case when". IMO this is heavy on maintenance. Try adding in another field or two.

Exec SQL
Insert into VOGIO/WUVT0547
Select
case when :IndKumVb = '1' or :IndKumKd = '1' then 0 else vsman end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr else 0 end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana1 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana2 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.acoi else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.apla else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.aort else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.astr else ' ' end,
case when :IndKumKd = '1' then vskdnr else 0 end,
case when :IndKumKd = '1' then kdslst else 0 end,
case when :IndKumKd = '1' then b.ana1 else ' ' end,
case when :IndKumKd = '1' then b.ana2 else ' ' end,
case when :IndKumKd = '1' or :IndKumLd = '1' then b.acoi else ' ' end,
case when :IndKumKd = '1' then b.apla else ' ' end,
case when :IndKumKd = '1' then b.aort else ' ' end,
case when :IndKumKd = '1' then b.astr else ' ' end,
case when :IndKumKd = '1' then vskdgr else ' ' end,
case when :IndKumKd = '1' then c.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslbr else ' ' end,
case when :IndKumKd = '1' then d.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslpt else ' ' end,
case when :IndKumKd = '1' or :IndKumVt = '1' then vsvtr else 0 end,
case when :IndKumKd = '1' or :IndKumVt = '1' then e.tabbez else ' '
end,
case when :IndKumKd = '1' then kdsbdw else ' ' end,
case when :IndKumKd = '1' then kdfu1 else 0 end,
// Teilenr, Bez, Status, Eigen-/Fremdfertigung, Prod.EAN, Karton.EAN, Zolltarif-Nr.
case when :IndKumTn = '1' then vstnr else ' ' end,
case when :IndKumTn = '1' then imdsc else ' ' end,
case when :IndKumTn = '1' then imsts else ' ' end,
case when :IndKumTn = '1' then imcode else ' ' end,
case when :IndKumTn = '1' then imrsv9 else ' ' end,
case when :IndKumTn = '1' then imrsv0 else ' ' end,
case when :IndKumTn = '1' then mi1swn else ' ' end,
// Preisgr, MarkenGruppe
case when :IndKumTn = '1' or :IndKumPr = '1' then vspgrg else ' ' end,
case when :IndKumTn = '1' or :IndKumPr = '1' then f.prbez else ' '
end,
case when :IndKumTn = '1' then vsmagr else ' ' end,
case when :IndKumTn = '1' then g.tabbez else ' ' end,
// MarkenHptGr, MarkeInd, Warengruppe
case when :IndKumTn = '1' then mi1v22 else ' ' end,
case when :IndKumTn = '1' then h.tabbez else ' ' end,
case when :IndKumTn = '1' then vsmark else ' ' end,
case when :IndKumTn = '1' then i.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then vsmrze else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then j.tabbez else ' '
end,
// ProdHptGr, ProdGr
case when :IndKumTn = '1' then vsprhg else ' ' end,
case when :IndKumTn = '1' then p.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then vsprgr else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then k.tabbez else ' '
end,
sum(vvimg3), sum(vvimg2), sum(vvimg1), sum(vvimg0),
sum(vviwg3), sum(vviwg2), sum(vviwg1), sum(vviwg0)
From VOGIO/wuStatistik Left Outer Join DCWD/AADRNU a
on vsvbnr = a.anum
Left Outer Join DCWD/AADRNU b
on vskdnr = b.anum
Left Outer Join VOGIO/KUNDST00
on vskdnr = kdnr
Left Outer Join VOGIO/V#TAK100 c
on vskdgr = c.tabinn
Left Outer Join VOGIO/V#TABR00 d
on kdslbr = d.tabinn
Left Outer Join VOGIO/V#TAVT00 e
on vsvtr = e.tabinn
Left Outer Join SIM400MFG/FKITMSTR
on vstnr = impn
Left Outer Join DCWD/MITEM
on vstnr = mi1mnr
Left Outer Join VOGIO/V#TAP999 f
on kdslpt = f.sort and vspgrg = f.preisg
Left Outer Join VOGIO/V#TAMG00 g
on vsmagr = g.tabind
Left Outer Join VOGIO/V#TAMH00 h
on mi1v22 = h.tabind
Left Outer Join VOGIO/V#TAMA00 i
on vsmark = i.tabind
Left Outer Join VOGIO/V#TAWG00 j
on vsmrze = j.tabinz
Left Outer Join VOGIO/V#TAPH00 p
on vsprhg = p.tabind
Left Outer Join VOGIO/V#TAPG00 k
on vsprgr = k.tabind
Left Outer Join VOGIO/V#TAPU00 l
on vsprug = l.tabind
Left Outer Join VOGIO/V#TAGB00 m
on vsgebi = m.tabind
Left Outer Join VOGIO/V#TATH00 n
on vsgeba = n.tabind
Left Outer Join VOGIO/V#TALI00 o
on mi1v19 = o.tabinz
Where
Case When :ikdgr <> 0 Then :ikdgr Else vskdgr end = vskdgr
And Case When :ikdbr <> 0 Then :ikdbr Else kdslbr end = kdslbr
And Case When :ikdland <> ' ' Then :ikdland Else vsbla end = vsbla
And vsvtr >= Case When :ivtrvon <> 0 Then :ivtrvon Else vsvtr end
And vsvtr <= Case When :ivtrbis <> 0 Then :ivtrbis Else vsvtr end
And Case When :iverb <> 0 Then vsvbnr else 9999999 end
in(Select stverb from WUVT0547VB)
And Case When :ikdnr <> 0 Then :ikdnr Else vskdnr end = vskdnr
And Case When :imagr <> ' ' Then :imagr Else vsmagr end
= vsmagr
And Case When :imhptgr <> ' ' Then :imhptgr Else
coalesce(mi1v22, ' ') end = coalesce(mi1v22, ' ')
And Case When :imarkind <> ' ' Then :imarkind Else vsmark end
= vsmark
And Case When :iwg <> ' ' Then :iwg Else vsmrze end = vsmrze
And Case When :iprodhgr <> ' ' Then :iprodhgr Else vsprhg end
= vsprhg
And Case When :iprodgr <> ' ' Then :iprodgr Else vsprgr end
= vsprgr
And Case When :iprodugr <> ' ' Then :iprodugr Else vsprug end
= vsprug
And Case When :igebgr <> ' ' Then :igebgr Else vsgebi end = vsgebi
And Case When :igebart <> ' ' Then :igebart Else vsgeba end
= vsgeba
And Case When :iuland <> ' ' Then :iuland Else
coalesce(mi1v19, ' ') end = coalesce(mi1v19, ' ')
And Case When :ipreisgr <> ' ' Then :ipreisgr Else vspgrg end
= vspgrg
And Case When :itnr <> ' ' Then :itnr Else vstnr end = vstnr
And Case When :istatus <> ' ' Then :istatus Else imsts end
= imsts
Group by
case when :IndKumVb = '1' or :IndKumKd = '1' then 0 else vsman end,
case when :IndKumVb = '1' or :IndKumKd = '1' then vsvbnr else 0 end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana1 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.ana2 else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.acoi else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.apla else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.aort else ' ' end,
case when :IndKumVb = '1' or :IndKumKd = '1' then a.astr else ' ' end,
case when :IndKumKd = '1' then vskdnr else 0 end,
case when :IndKumKd = '1' then kdslst else 0 end,
case when :IndKumKd = '1' then b.ana1 else ' ' end,
case when :IndKumKd = '1' then b.ana2 else ' ' end,
case when :IndKumKd = '1' or :IndKumLd = '1' then b.acoi else ' ' end,
case when :IndKumKd = '1' then b.apla else ' ' end,
case when :IndKumKd = '1' then b.aort else ' ' end,
case when :IndKumKd = '1' then b.astr else ' ' end,
case when :IndKumKd = '1' then vskdgr else ' ' end,
case when :IndKumKd = '1' then c.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslbr else ' ' end,
case when :IndKumKd = '1' then d.tabbez else ' ' end,
case when :IndKumKd = '1' then kdslpt else ' ' end,
case when :IndKumKd = '1' or :IndKumVt = '1' then vsvtr else 0 end,
case when :IndKumKd = '1' or :IndKumVt = '1' then e.tabbez else ' '
end,
case when :IndKumKd = '1' then kdsbdw else ' ' end,
case when :IndKumKd = '1' then kdfu1 else 0 end,
// Teilenr, Bez, Status, Eigen-/Fremdfertigung, Prod.EAN, Karton.EAN, Zolltarif-Nr.
case when :IndKumTn = '1' then vstnr else ' ' end,
case when :IndKumTn = '1' then imdsc else ' ' end,
case when :IndKumTn = '1' then imsts else ' ' end,
case when :IndKumTn = '1' then imcode else ' ' end,
case when :IndKumTn = '1' then imrsv9 else ' ' end,
case when :IndKumTn = '1' then imrsv0 else ' ' end,
case when :IndKumTn = '1' then mi1swn else ' ' end,
// Preisgr, MarkenGruppe
case when :IndKumTn = '1' or :IndKumPr = '1' then vspgrg else ' ' end,
case when :IndKumTn = '1' or :IndKumPr = '1' then f.prbez else ' '
end,
case when :IndKumTn = '1' then vsmagr else ' ' end,
case when :IndKumTn = '1' then g.tabbez else ' ' end,
// MarkenHptGr, MarkeInd, Warengruppe
case when :IndKumTn = '1' then mi1v22 else ' ' end,
case when :IndKumTn = '1' then h.tabbez else ' ' end,
case when :IndKumTn = '1' then vsmark else ' ' end,
case when :IndKumTn = '1' then i.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then vsmrze else ' ' end,
case when :IndKumTn = '1' or :IndKumWG = '1' then j.tabbez else ' '
end,
// ProdHptGr, ProdGr
case when :IndKumTn = '1' then vsprhg else ' ' end,
case when :IndKumTn = '1' then p.tabbez else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then vsprgr else ' ' end,
case when :IndKumTn = '1' or :IndKumPG = '1' then k.tabbez else ' '
end;

[AADITYA]

Re: Order By with Case

HI EVERYONE, well i am new to this forum but i am using this to get the solution for my query and really you guys are just amazing...Thank You for all your help and support...and wishing u all a very Happy and a color full Holi...LOVE ;-)