Toad World® Forums

Case in where Clause of select

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;

Your where clause CASE statements are missing the “END”. You have them in the select, but not in the where.

From: winona [mailto:bounce-winona@toadworld.com]

Sent: Monday, January 06, 2014 3:50 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Case in where Clause of select

Case
in where Clause of select

Thread created by winona

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;

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

You mean the endcase staement is missing in my query?

Well, not exactly.

end case” is plsql syntax and in sql (like it is your case) there is only “end” reserved word.

Hope this helps you.

Brg

Damir Vadas

I gave end and i also changed the query like this

and B.ACCOUNTING_DT =

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

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

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

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

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

But this did not help. It still says “missing keyword” when i execute this.

Okay i changed like this

and B.ACCOUNTING_DT =

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

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

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

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

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

AND B.ACCOUNT IN (‘1625’,

’ ')

AND B.ANALYSIS_TYPE <> ‘BUD’

But i get ORA-00907: missing right parenthesis error.

Try this:

and B.ACCOUNTING_DT = CASE
WHEN B.ACCOUNTING_DT < '01-Oct-2013’THEN PROJ_BAL
WHEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’)THEN ACTUAL
WHEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’) THEN “CLOSE”
WHEN B.ACCOUNTING_DT BETWEEN (‘01-OCT-2013’) AND (‘31-OCT-2013’) THEN EXPENSE
– WHEN B.ACCOUNTING_DT <= ‘31-Oct-2013’ THEN PROJ_BAL_END – this is repeated filter???
WHEN B.ACCOUNTING_DT > ‘31-Oct-2013’ THEN PROJ_BAL_END
ELSE ‘???’
END
AND B.ACCOUNT IN (‘1625’,
’ ')
AND B.ANALYSIS_TYPE <> ‘BUD’