Hi All,
I have a problem with my CASE statement. I am checking if any of you have encountered the error...
Basically, What I am trying to do is....
IF GLDEPTCD = '01' THEN Metric1
ELSIF GLDEPTCD = '09' THEN Metric9.
Please ASSUME that the SQL recognises all the temporary tables that starts with "T1Y" in the query. I am sending only the final pass of the query. The first four passes works like charm.
The ERROR.... I tried the same report on two different data bases (Oracle, DB2)and the error is same in both. Right now, I am pointed to Oracle and the error is "NOT A GROUP BY EXPRESSION".
Here is the final pass of the SQL which is causing the problem....
------------------------------------------------------
select pa1.GLDEPTCD GLDEPTCD,
pa1.ADWKID ADWKID,
CASE WHEN a14.DEPTGRP_NO = '01' THEN NVL((pa1.WJXBFS1 * (1.0 / (CASE WHEN (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0))=0 THEN NULL ELSE (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) END))), 0) WHEN a14.DEPTGRP_NO = '09' THEN NVL((pa3.WJXBFS1 * (1.0 / (CASE WHEN (NVL(pa3.WJXBFS1, 0) + NVL(pa4.WJXBFS1, 0))=0 THEN NULL ELSE (NVL(pa3.WJXBFS1, 0) + NVL(pa4.WJXBFS1, 0)) END))), 0) END WJXBFS1
from ZZMD00 pa1
join ZZMD01 pa2
on (pa1.ADWKID = pa2.ADWKID and
pa1.GLDEPTCD = pa2.GLDEPTCD)
join ZZMD02 pa3
on (pa1.ADWKID = pa3.ADWKID and
pa1.GLDEPTCD = pa3.GLDEPTCD)
join ZZMD03 pa4
on (pa1.ADWKID = pa4.ADWKID and
pa1.GLDEPTCD = pa4.GLDEPTCD)
join DWADMV.STORINVC a11
on (pa1.GLDEPTCD = a11.GLDEPTCD)
join DWADMV.STORINVCITM a12
on (a11.BUSDY = a12.BUSDY and
a11.INVCNBR = a12.INVCNBR)
join VMSI.ITM a13
on (a12.ITMNBR = a13.ITMNBR and
a12.WHSECD = a13.WHSECD)
join VMSI.MCHDEPTDESC a14
on (a13.DEPT_RN = a14.DEPT_NO)
where pa1.GLDEPTCD in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '35')
group by pa1.GLDEPTCD,
pa1.ADWKID
------------------------------------------------------
I think there is something very basic that I am missing. Please suggest me the alternative for the above CASE statement. Any help is highly appreciated.
- Leo
I have a problem with my CASE statement. I am checking if any of you have encountered the error...
Basically, What I am trying to do is....
IF GLDEPTCD = '01' THEN Metric1
ELSIF GLDEPTCD = '09' THEN Metric9.
Please ASSUME that the SQL recognises all the temporary tables that starts with "T1Y" in the query. I am sending only the final pass of the query. The first four passes works like charm.
The ERROR.... I tried the same report on two different data bases (Oracle, DB2)and the error is same in both. Right now, I am pointed to Oracle and the error is "NOT A GROUP BY EXPRESSION".
Here is the final pass of the SQL which is causing the problem....
------------------------------------------------------
select pa1.GLDEPTCD GLDEPTCD,
pa1.ADWKID ADWKID,
CASE WHEN a14.DEPTGRP_NO = '01' THEN NVL((pa1.WJXBFS1 * (1.0 / (CASE WHEN (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0))=0 THEN NULL ELSE (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) END))), 0) WHEN a14.DEPTGRP_NO = '09' THEN NVL((pa3.WJXBFS1 * (1.0 / (CASE WHEN (NVL(pa3.WJXBFS1, 0) + NVL(pa4.WJXBFS1, 0))=0 THEN NULL ELSE (NVL(pa3.WJXBFS1, 0) + NVL(pa4.WJXBFS1, 0)) END))), 0) END WJXBFS1
from ZZMD00 pa1
join ZZMD01 pa2
on (pa1.ADWKID = pa2.ADWKID and
pa1.GLDEPTCD = pa2.GLDEPTCD)
join ZZMD02 pa3
on (pa1.ADWKID = pa3.ADWKID and
pa1.GLDEPTCD = pa3.GLDEPTCD)
join ZZMD03 pa4
on (pa1.ADWKID = pa4.ADWKID and
pa1.GLDEPTCD = pa4.GLDEPTCD)
join DWADMV.STORINVC a11
on (pa1.GLDEPTCD = a11.GLDEPTCD)
join DWADMV.STORINVCITM a12
on (a11.BUSDY = a12.BUSDY and
a11.INVCNBR = a12.INVCNBR)
join VMSI.ITM a13
on (a12.ITMNBR = a13.ITMNBR and
a12.WHSECD = a13.WHSECD)
join VMSI.MCHDEPTDESC a14
on (a13.DEPT_RN = a14.DEPT_NO)
where pa1.GLDEPTCD in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '35')
group by pa1.GLDEPTCD,
pa1.ADWKID
------------------------------------------------------
I think there is something very basic that I am missing. Please suggest me the alternative for the above CASE statement. Any help is highly appreciated.
- Leo
