Hi All,

I am having an issue in the where clause of this select. please let me know how to do it. For the field B.ACCOUNTING_DT i have added case but it is throwing exception.

SELECT BUSINESS_UNIT,

PROJECT_ID,

INTEGRATION_TMPL,

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’ THEN SUMMED_AMTS END), 0) -

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’ THEN SUMMED_AMTS END), 0) -

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’ THEN SUMMED_AMTS END), 0) PROJ_BAL,

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’ THEN SUMMED_AMTS END), 0) ACTUAL,

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’ THEN SUMMED_AMTS END), 0) CLOSE,

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’ THEN SUMMED_AMTS END), 0) EXPENSE,

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘ACT’ THEN SUMMED_AMTS END), 0) -

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘CLS’ THEN SUMMED_AMTS END), 0) -

NVL (SUM (CASE WHEN ANALYSIS_TYPE = ‘EXP’ THEN SUMMED_AMTS END), 0) PROJ_BAL_END

FROM

(SELECT A.BUSINESS_UNIT,

A.PROJECT_ID,

A.INTEGRATION_TMPL,

SUM(B.RESOURCE_AMOUNT)SUMMED_AMTS,

B.ANALYSIS_TYPE

FROM sysadm.PS_PROJECT A,

sysadm.PS_PROJ_RESOURCE B

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT

AND A.PROJECT_ID = B.PROJECT_ID

AND A.BUSINESS_UNIT = ‘PCCAP’

AND A.PROJECT_TYPE IN (‘CAPTL’,

‘CSP’,

‘VEND’) and B.ACCOUNTING_DT =

(CASE WHEN PROJ_BAL THEN B.ACCOUNTING_DT < ‘01-Oct-2013’),

(CASE WHEN ACTUAL THEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’)),

(CASE WHEN CLOSE THEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’)),

(CASE WHEN EXPENSE THEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’)),

(CASE WHEN PROJ_BAL_END THEN B.ACCOUNTING_DT <= ‘31-Oct-2013’),

AND B.ACCOUNT IN (‘1625’,

’ ')

AND B.ANALYSIS_TYPE <> ‘BUD’

GROUP BY A.BUSINESS_UNIT,

A.PROJECT_ID,

A.INTEGRATION_TMPL,

B.ANALYSIS_TYPE)

GROUP BY BUSINESS_uNIT,

PROJECT_ID,

INTEGRATION_TMPL;