Is ICASE() has a limit? 1

[TheLazyPig]

I used ICASE() in my query to avoid duplicate data.

ICASE(rde.code="1","ADB",rde.code="2","AI",rde.code="3","WPD",rde.code="4","PC",rde.code="5","PCWPD",rde.code="6","5YRT",rde.code="7","ISR",rde.code="8","10YRT",rde.code="9","ADB/MA",rde.code="A","10PER",rde.code="B","15PER",rde.code="C","5YRT/WPD",rde.code="D","ISR/WPD",rde.code="E","10ROP",rde.code="F","20ROP",rde.code="G","10SL",rde.code="H","15SL",rde.code="I","10PER/WPD",rde.code="J","10SL/WPD",rde.code="K","15PER/WPD",rde.code="L","15SL/WPD",rde.code="M","10ROP/WPD",rde.code="N","20ROP/WPD",rde.code="O","PC/WPD(PDF)",rde.code="P","PBR",rde.code="Q","CEFR",rde.code="R","GBR",rde.code="S","SFAR",rde.code="Z","HIR","") RIDER

When I try to use this I got error but if I lessen the data it works.

Is ICASE() limited?

Here is my query...

SELECT pms.policy + "" + pms.suffix AS POLNO
,RTRIM(dtl.fn) + ", " + RTRIM(dtl.gn) + " " + RTRIM(dtl.mi) AS NAME
,pst.statdesc AS STATUS
,pln.desc AS PLANDESC
,scf.cov_amt AS FACEAMT ,ICASE(rde.code="1","ADB",rde.code="2","AI",rde.code="3","WPD",rde.code="4","PC",rde.code="5","PCWPD",rde.code="6","5YRT",rde.code="7","ISR",rde.code="8","10YRT",rde.code="9","ADB/MA",rde.code="A","10PER",rde.code="B","15PER",rde.code="C","5YRT/WPD",rde.code="D","ISR/WPD",rde.code="E","10ROP",rde.code="F","20ROP",rde.code="G","10SL",rde.code="H","15SL",rde.code="I","10PER/WPD",rde.code="J","10SL/WPD",rde.code="K","15PER/WPD",rde.code="L","15SL/WPD",rde.code="M","10ROP/WPD",rde.code="N","20ROP/WPD",rde.code="O","PC/WPD(PDF)",rde.code="P","PBR",rde.code="Q","CEFR",rde.code="R","GBR",rde.code="S","SFAR",rde.code="Z","HIR","") RIDER
,scf.premium AS RATE
,pms.premium
,ROUND((scf.premium*(scf.cov_amt/1000)),2) AS PREM_RDE
,ROUND((pms.premium-(scf.premium*(scf.cov_amt/1000))),2) AS BASIC
,MAX(pms.stat_date) AS STATDATE
FROM pmaster pms
LEFT JOIN polstat pst ON pst.statcode = pms.status
LEFT JOIN scfile scf ON scf.policy + "" + scf.suffix = pms.policy + "" + pms.suffix
LEFT JOIN plancode pln ON pln.plan = pms.plan_code
LEFT JOIN riderfle rde ON rde.code = scf.rider
LEFT JOIN ppfile dtl ON dtl.policy + "" + dtl.suffix = pms.policy + "" + pms.suffix
WHERE pms.policy + "" + pms.suffix = '00004779P'
AND pst.statcode IN ('1','3','4','8','12')
GROUP BY pms.policy,pms.suffix,dtl.fn,dtl.gn,dtl.mi,pst.statdesc,pln.desc,rde.code,scf.cov_amt,scf.premium,pms.premium

Maybe it's in my query...

Thank you.

 

[mjcmkrsr]

Hi,

1) ICASE is limited to 100 pairs

2) There is a typo in your SELECT

,ICASE(rde.code="1","ADB",rde.code="2","AI",rde.code="3","WPD",rde.code="4","PC",rde.code="5","PCWPD",rde.code="6","5YRT",rde.code="7","ISR",rde.code="8","10YRT",rde.code="9","ADB/MA",rde.code="A","10PER",rde.code="B","15PER",rde.code="C","5YRT/WPD",rde.code="D","ISR/WPD",rde.code="E","10ROP",rde.code="F","20ROP",rde.code="G","10SL",rde.code="H","15SL",rde.code="I","10PER/WPD",rde.code="J","10SL/WPD",rde.code="K","15PER/WPD",rde.code="L","15SL/WPD",rde.code="M","10ROP/WPD",rde.code="N","20ROP/WPD",rde.code="O","PC/WPD(PDF)",rde.code="P","PBR",rde.code="Q","CEFR",rde.code="R","GBR",rde.code="S","SFAR",rde.code="Z","HIR","")
[highlight #EF2929][COLOR=#FCE94F]AS[/color][/highlight] RIDER

3) Furthermore you may want to change the GROUP BY in order to consider the computed fields

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

4) You may also want to create a 2 field table/cursor with the values of the ICASE statement and join that table/cursor in your SQL SELECT

5) The RIDER field should be of fixed length e.g. ICASE(rde.code = "1", "ABD___" ...) since SQL SELECT determines the length of that field on the first field found - or you even may want to use the CAST function

6) Finally I suggest to delete the +""+ from the JOIN conditions e.g. LEFT JOIN scfile scf ON scf.policy + scf.suffix = pms.policy + pms.suffix

hth
MarK

 

[TheLazyPig]

Thank you for the respond.

1) The data I used is less than 100 so it's ok.

2) I removed the Alias for Rider.

3) I did not include Group By in my query.

4) I do want to create another column for Rider. it should be three columns because the data I'm getting is like this

5) I tried to re-arrange the position of the rider in ICASE, I started with

ICASE(rde.code="I","10PER/WPD",rde.code="J","10SL/WPD",....)

It only get me the three characters instead of all. In regards to CAST function, I haven't used the function yet so I'm searching for examples.

6) I deleted " " it still work.

I don't know what happen but it suddenly works. Thank you Thank you

 

[mjcmkrsr]

Hi,

3) I did not include Group By in my query.

You'll have to since you're using the aggregate function MAX(...)

In regards to CAST function, I haven't used the function yet so I'm searching for examples.

Select  ... ,
CAST(ICASE(rde.code = "1", "ABD", ..... , "") as C(10)) AS RIDER,
...

hth

MarK

 

[TheLazyPig]

I forgot to say that I removed also the MAX() but then I decided to add it again and the Group By.

I learned another function. Thank you again

 

[Mukesh Khandelwal]

Hi

i think here in ICASE you have to take same length for all Description as per rde.code like

ICASE(rde.code="1","ADB",rde.code="2","AI",rde.code="3","WPD",rde.code="4","PC",rde.code="5","PCWPD",rde.code="6","5YRT",rde.code="7","ISR",rde.code="8","10YRT",rde.code="9","ADB/MA",rde.code="A","10PER",rde.code="B","15PER",rde.code="C","5YRT/WPD",rde.code="D","ISR/WPD",rde.code="E","10ROP",rde.code="F","20ROP",rde.code="G","10SL",rde.code="H","15SL",rde.code="I","10PER/WPD",rde.code="J","10SL/WPD",rde.code="K","15PER/WPD",rde.code="L","15SL/WPD",rde.code="M","10ROP/WPD",rde.code="N","20ROP/WPD",rde.code="O","PC/WPD(PDF)",rde.code="P","PBR",rde.code="Q","CEFR",rde.code="R","GBR",rde.code="S","SFAR",rde.code="Z","HIR","") in this all description column

"ADB "
"AI "
"WPD "
"10PER/WPD"
"10SL/WPD "..... all should be of Maximum length here character(9)

Thanks

Mukesh Khandelwal

 

[Olaf Doschke]

If you need such an icase it speaks for creating a table or cursor to join on the rde.code

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name