Getting rid of hardcosing in case query

Database
Enthusiast

Getting rid of hardcosing in case query

Hi There

I have a Case statement below where I have 4 differnt types of ACC_SRCE_PROD_CDE_FK hardcoded into the statment. I would like to susbstitute that part of the statement to avoid hardcoding values  ('CDO2', 'CDO3', 'CL01', 'B2B2') into the statement Is it this posssible to do? I have thought about putting all the conditions in the CASE clause into the WHERE part of the statement(see below case clause) but these will contradict with some of the conditions i already have in there. i.e CRDM.A_NET_BAL_F_L_DR=0

CASE

WHEN (FPPD.UDA_2='Y'

OR (CRDM.ACC_SRCE_PROD_CDE_FK = 'FFFACB'

AND CRDM.A_NET_BAL_F_L_DR < 0))

AND (CRDM.ACC_SRCE_PROD_CDE_FK<>'UNFLCB' OR CRDM.A_NET_BAL_F_L_DR<0) 

AND CRDM.ACC_SRCE_PROD_CDE_FK NOT IN ('CDO2', 'CDO3', 'CL01', 'B2B2')

THEN CRDM.A_NET_BAL_F_L_DR

ELSE 0

WHERE (FLF.PERIOD_DTE='2012-06-29'

AND FLF.MARKET_SEGMENT_CDE=5

AND FLF.FACILITY_NO NOT IN (SELECT FACILITY_NO FROM DDEWP42P.FMP_CM_EXCLUDE_FACILITY_REF))

AND (CRDM.ACC_NO IS NOT NULL)

AND (NOT(FLF.FAC_NET_LIMIT_FOR_PRINCPL_EUR = 0 AND CRDM.A_NET_BAL_F_L_DR= 0));

Any help on this would be very much appreciated

Regards

Donal

1 REPLY
Enthusiast

Re: Getting rid of hardcosing in case query

One suggestion out of many possibilities:

Put the values in a table and rewrite the query to outer join to the new table then use a simpler CASE to determine the output based upon the presense/absence of a joined row.