All Forums Enterprise
marcmc 112 posts Joined 12/05
06 Jul 2007
CASE Statement

I basically want to add a case statement to say if rated_driver_id Is Not Null AND rated_P_driver_id Is Null AND 'H' || trim(t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') then COALESCE(rated_P_driver_id,0). The below gives me an error as follows, what can I do to get the syntax correct?3707: Syntax Error, expecting something like a 'THEN' keyword between ')' and '('.SELECT top 5 CASE WHEN rated_driver_id Is Not Null AND rated_P_driver_id Is Null AND 'H' || trim (t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N' THEN NULL ELSE CASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_id WHEN c.Product_Type = 'QMP' THEN r.rated_driver_id ELSE NULL END END) THEN COALESCE(rated_P_driver_id,0) rated_p_driver_id, c.tr_single_driver, c.mod_policy_holder, Policy_Holder_Discount FROM po_risk_detail_cursor c LEFT JOIN tmp_po_rsk_dtl_sales_rep t......

marcmc 112 posts Joined 12/05
06 Jul 2007

Also it looks like it doesn't like the coalesce statement within caseIs this possible?

j355ga 100 posts Joined 12/05
06 Jul 2007

I'd guess you need to parenthesis the nested case statements and the coalesce. example:select case when foo is not null then ( case when bar ='x' then ( case when blah=1 then (coalesce(whatever,1)) else 2 end ) else null end )end

Jeff

Fred 579 posts Joined 08/04
06 Jul 2007

Be sure your CASE, WHEN/THEN, ELSE, END keywords match up properly; for example:SELECT top 5 CASE WHEN rated_driver_id Is Not Null AND rated_P_driver_id Is Null AND 'H' || trim (t7.corrected_product_type) in ('HQMV', 'HQMF', 'HQMP', 'HQMB') THEN (CASE WHEN c.trans_seq_no IS NULL AND c.no_qmrneg_det = 'N' THEN NULLELSECASE WHEN r9.rated_driver_id IS NOT NULL THEN r9.rated_driver_idWHEN c.Product_Type = 'QMP' THEN r.rated_driver_idELSE NULLENDEND) THEN ELSE COALESCE(rated_P_driver_id,0) END rated_p_driver_id,c.tr_single_driver, c.mod_policy_holder, Policy_Holder_Discount FROM po_risk_detail_cursor c LEFT JOIN tmp_po_rsk_dtl_sales_rep t

marcmc 112 posts Joined 12/05
09 Jul 2007

thanks all

shokap 9 posts Joined 02/11
25 Apr 2011

I have a Insert select query like - Insert tatement - 7 Select query with UNION ALL clause between them .All the SELECT CLAUSE have identical , FROM AND WHERE CLAUSE CONDITION.

(FCF01 being changed to FCF02 ..FCF07) respectively having UNION ALL Clause in between.And have identical FROM Clause.Was just wondering insead of 7 UNION ALL clause for 2-3 columns ? Is there any other alternatice,i.e. using CASE statement ?? Pease suggest.

E.g. First query is given below -

SELECT
FCC.FEC_PROC AS FEC_PROC
,FCC.COD_PERSONA AS COD_PERSONA
,FCC.IND_MODELO AS IND_MODELO
,'COR' AS IND_MOD_ORIGEN
,FCC.FACTOR_FCF01 AS ID_FACTOR
,CAST(FCC.SCORE_FCF01 AS DECIMAL(21,8)) AS SCORE_FACTOR
FROM VAR_COR COR
INNER JOIN CUANT FCC
ON COR.FEC_PROC = FCC.FEC_PROC
AND COR.COD_PERSONA = FCC.COD_PERSONA

RB185048 32 posts Joined 09/08
25 Apr 2011

best way i think is define a table which will have all 7 columns (FCF01 to FCF07) along with other columns.So this will be master table.
Then do a
Insert into <>
sel other columns,FCF01 from <> ;
Insert into <>
sel other columns,FCF02 from <> ;
like this 7 statements
Instead of 7 union all statements.
In this way you do not have to perform INNER JOINS 7 times.
hope this will improve performance.
CASE STATEMENT may not help here.

You must sign in to leave a comment.