CASE Statement

UDA
Highlighted
Enthusiast

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
......
6 REPLIES
Enthusiast

Re: CASE Statement

Also it looks like it doesn't like the coalesce statement within case
Is this possible?
Teradata Employee

Re: CASE Statement

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

Teradata Employee

Re: CASE Statement

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 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 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
Enthusiast

Re: CASE Statement

thanks all
Enthusiast

Re: CASE Statement

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
Enthusiast

Re: CASE Statement

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.