Case Statement Help Please

Hello,
I have tried the following syntax:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE
WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
END
and it is not working - I get the error of
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
I'm not sure what is missing/wrong on that line?
Thanks!Your syntax is wrong, the THEN clause of a CASE cannot be a boolean
expression -- it must be scalar. Please refer to SQL Server Books Online for
syntax details.
Based on a brief glance through the code, the comparable t-SQL code would be
something along the lines of:
UPDATE stage_phx_fact_policy_rf
SET wind = ( SELECT short_code_2
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number )
WHERE occupancy = 'poultry'
AND EXISTS ( SELECT *
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number ) ;
If only unique columns are participating in the correlation, you can use
t-SQL FROM clause as well.
Anith|||Patrice,
You have to put the END statement at the point you are finished with the
CASE clause:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
END
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
John Scragg
"Patrice" wrote:

> Hello,
> I have tried the following syntax:
> UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
> SET WIND =
> CASE
> WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
> THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
> WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
> WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
> END
>
>
>
> and it is not working - I get the error of
> Server: Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near '='.
>
> I'm not sure what is missing/wrong on that line?
> Thanks!
>