TheLazyPig
Programmer
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.
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.