Case in where Clause of select

Toad for Oracle & DevOps Toolkit
1

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;

2

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.

3

You mean the endcase staement is missing in my query?

4

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

5

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.

6

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.

7

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’