Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is ICASE() has a limit? 1

Status
Not open for further replies.

TheLazyPig

Programmer
Sep 26, 2019
94
PH
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.
 
Hi,

1) ICASE is limited to 100 pairs

2) There is a typo in your SELECT

Code:
,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

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

imageeee_abeeq6.jpg


5) I tried to re-arrange the position of the rider in ICASE, I started with
Code:
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 [smile] [smile]
 
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.

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

hth

MarK
 
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 [thumbsup2]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top