How to use CASE in a WHERE statement correctly?

[lmctech]

I cant seem to get this to work correctly. I am using an oracle backend and the code will be tossed into a Crystal command when done.

Can someone point me in the right direction please?

My code is as follows:

SELECT
(SELECT
MAX(K0.TRANSACTION_DATE)
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID) DATA_DATE,
CASE
WHEN LENGTH(Y.SOURCE)<1
OR Y.SOURCE IS NULL
THEN 'OTHER'
ELSE
Y.SOURCE END AS SOURCE,
CASE
WHEN Y.SOURCE='BRANCH' THEN 1 ELSE 0 END AS BRANCH_SOURCE,
Y.RCL_MGR,
Y.SR_RCL,
Y.RCL,
Y.APPLICATION_NBR,
Y.PRODUCT,
Y.PRODUCT_NAME_CATEGORY,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'AUTO' OR Y.PRODUCT_NAME_CATEGORY='RV' THEN 'AUTO/RV'
ELSE Y.PRODUCT_NAME_CATEGORY END) PRODUCT_NAME_CATEGORY2,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'HELOC' OR Y.PRODUCT_NAME_CATEGORY='HOME EQ FIXED' THEN 'LINES AND LOANS'
ELSE 'OTHER' END) PRODUCT_GROUPING,
Y.PRODUCT_NAME_DETAIL,
Y.REQ_DATE,
Y.DEC_DATE,
Y.APR_DATE,
Y.STORE_NAME,
Y.REGION_NAME,
Y.MARKET_NAME,
Y.MARKET_NAME_PLAIN,
Y.METRO_NAME,
Y.DAY1_START,
Y.DAY1_END,
Y.MTD_START,
Y.MTD_END,
Y.YTD_START,
Y.YTD_END,
Y.LMTD_START,
Y.LMTD_END,
Y.LYTD_START,
Y.LYTD_END,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) REQ_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN 1 ELSE 0 END) REQ_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN 1 ELSE 0 END) REQ_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) REQ_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) REQ_LMTD_IN,

(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LMTD_IN,

(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) DEC_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN 1 ELSE 0 END) DEC_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN 1 ELSE 0 END) DEC_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) DEC_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) DEC_LMTD_IN,

(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) FUN_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN 1 ELSE 0 END) FUN_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN 1 ELSE 0 END) FUN_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) FUN_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) FUN_LMTD_IN,

(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LMTD_IN,

(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LMTD_IN,

(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) APR_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN 1 ELSE 0 END) APR_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN 1 ELSE 0 END) APR_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) APR_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) APR_LMTD_IN,

(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LMTD_IN

FROM
(
SELECT
K1.START_DATE AS DATA_DATE,
K1.APPLICATION_NBR APPLICATION_NBR,
K1.Product Product,
K1.PRODUCT_NAME_CATEGORY PRODUCT_NAME_CATEGORY,
K1.PRODUCT_NAME_DETAIL PRODUCT_NAME_DETAIL,
UPPER(CLRR.CLRR_STORE_DESC) STORE_NAME,
UPPER(CLRR.CLRR_REGION_DESC) REGION_NAME,
CASE
WHEN UPPER(CLRR.CLRR_MARKET_DESC) = 'UNKNOWN MARKET' THEN 'ZUNKNOWN MARKET' ELSE UPPER(CLRR.CLRR_MARKET_DESC) END AS MARKET_NAME,
UPPER(CLRR.CLRR_MARKET_DESC) MARKET_NAME_PLAIN,
UPPER(CLRR.METRO_NAME) METRO_NAME,
X.DAY1_START,
X.DAY1_END,
X.MTD_START,
X.MTD_END,
X.YTD_START,
X.YTD_END,
X.LMTD_START,
X.LMTD_END,
X.LYTD_START,
X.LYTD_END,
SOURCE.SOURCE,
CLRR.RCL_MGR,
CLRR.SR_RCL,
CLRR.RCL,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START)
AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)
THEN K0.ORIG_REQUESTED_AMT ELSE 0 END REQ_DOLLAR_AMT,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END DEC_DOLLAR_AMT,

/*CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
THEN K0.Booked_Amt ELSE 0 END FUN_DOLLAR_AMT,*/

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
AND UPPER(K1.PRODUCT) <> 'PLOC'
THEN K0.Contract_Financed_Amt
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
AND UPPER(K1.PRODUCT) = 'PLOC'
THEN K0.CREDIT_CARD_REQ_LIMIT
ELSE 0 END FUN_DOLLAR_AMT,



CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END APR_DOLLAR_AMT,

CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND (TRUNC(T_REQ.DAY_DATE) >= X.LYTD_START
AND TRUNC(T_REQ.DAY_DATE) <= X.YTD_END)
THEN TRUNC(T_REQ.DAY_DATE)
ELSE TO_DATE('1900-01-01')END REQ_DATE,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('1900-01-01') END DEC_DATE,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Funding_Complete_TS)
ELSE TO_DATE('1900-01-01') END FUN_DATE,

CASE
WHEN
TO_NUMBER(P.APPLICATION_STATUS_CODE ) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('1900-01-01') END APR_DATE

FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID
LEFT JOIN WHSUSR.VW_RPT_TIME_CALENDAR T_REQ
ON K1.DAY__APPLICATION_DATE_OID = T_REQ.BE_ID

LEFT JOIN
(SELECT
STORE_OID,
METRO_NAME,
CLRR_MARKET_DESC,
CLRR_REGION_DESC,
CLRR_STORE_DESC,
COALESCE(REGION_CONSUMER_LENDER_MGR, 'UNKNOWN') RCL_MGR,
COALESCE(SR_REGIONAL_CONSUMER_LENDER,'UNKNOWN') SR_RCL,
COALESCE(REGIONAL_CONSUMER_LENDER_I, 'UNKNOWN' ) RCL
FROM WHSUSR.VW_RPT_STORE_CLRR) CLRR

ON K0.STORE_OID = CLRR.STORE_OID

LEFT JOIN
(SELECT
BE_ID,
CASE
WHEN LENGTH(APP_SOURCE_GROUP)<1 THEN 'OTHER'
ELSE
APP_SOURCE_GROUP END AS SOURCE
FROM WHSUSR.VW_CURR_MAP_APPLICATION_SOURCE
WHERE APPLICATION_SOURCE <> '$NOOID' AND
APP_SOURCE_GROUP <> 'Not classified at this level') SOURCE
ON K1.APPLICATION_SOURCE_OID=SOURCE.BE_ID

LEFT JOIN
(SELECT
BE_ID,
APPLICATION_STATUS,
APPLICATION_STATUS_NAME,
APPLICATION_STATUS_CODE
FROM
WHSUSR.VW_CURR_MAP_APPLICATION
WHERE APPLICATION_STATUS <> '$NOOID'
AND TO_NUMBER(REPLACE(APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7) P
ON K1.APPLICATION_STATUS_OID = P.BE_ID

LEFT JOIN
(SELECT
SYSDATE AS XSYS,
D.DAY1_S AS DAY1_START,
D.DAY1_E AS DAY1_END,
trunc(D.DAY1_S,'mm') AS MTD_START,
D.DAY1_E AS MTD_END,
TRUNC(LAST_DAY(D.DAY1_S)-32,'MM') as LMTD_START,
ADD_MONTHS(D.DAY1_S,-1) AS LMTD_END,
FI.FYSD AS YTD_START,
D.DAY1_E AS YTD_END,
FI.LFYSD AS LYTD_START,
ADD_MONTHS(D.DAY1_E,-12) AS LYTD_END
/*CALENDAR YR INFO
TRUNC(D.DAY1_S,'YYYY') AS YTD_START,
D.DAY1_E AS YTD_END,
TRUNC(TRUNC(D.DAY1_S,'YYYY') -1,'YYYY') AS LYTD_START,
ADD_MONTHS(D.DAY1_E,-12) AS LYTD_END,*/
FROM
(SELECT
trunc({?LD},'DD') AS DAY1_S,
TRUNC(trunc({?LD},'DD') +1 -(1/24/60/60)) AS DAY1_E
FROM DUAL)D
/*find fiscal year start-end */
LEFT JOIN
(SELECT
F.DAY_DATE FID,
fiscal_year_start_date FYSD,
fiscal_year_end_date FYED,
ADD_MONTHS(fiscal_year_start_date,-12) LFYSD,
ADD_MONTHS(fiscal_year_end_date,-12) LFYED
FROM
WHSUSR.VW_RPT_TIME_FISCAL F
WHERE
To_char({?LD},'RRRR/MM/DD') =(F.day_date)) FI
ON
To_char(D.DAY1_S,'RRRR/MM/DD') =FI.FID)X
ON
TRUNC(X.XSYS) >= TRUNC(K1.START_DATE)
AND TRUNC(X.XSYS) < TRUNC(K1.END_DATE)

WHERE
(CASE
WHEN {?SOURCE} =1 THEN SOURCE='BRANCH' ELSE Y.SOURCE<> '' END) AND
(((TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END))
OR
((TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END))
OR
(TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START) AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)))
) Y

 

[Thargy]

lmc,

What error messages are you getting and what part or parts is/are wrong?

my first reaction is that your query is a whopper.

You have selection from subselects, which will probably perform very poorly.

Can you break it down into smaller chunks, provide us with create table statements and insert statements to load sample data. Then we can tackle the issue(s) of the chunks, and finally join them together at the end.

If you're just having syntax trouble, then I suggest you tackle each part individually, geet it working, and then join them together.

Regards

T

 

[lmctech]

The query runs fine and I get data. I am stuck on this one specific piece syntax-wise:

WHERE
(CASE
WHEN {?SOURCE} =1 THEN SOURCE='BRANCH' ELSE Y.SOURCE<> '' END) AND .....

I want to have the user select if they want to see 'branch data only' or 'all sources' (which would need to basically just set to TRUE so that all will come in).