CASE within a CASE

[TMiller16]

Here is the code....

CASE WHEN BD.VIN = S.VIN THEN S.Dlr_GMF_ID ELSE "" END AS SOLD_DEALER_CODE_Cognos,

CASE WHEN BD.BIDDER_DEALER_CODE = (CASE WHEN BD.VIN = S.VIN THEN S.Dlr_GMF_ID ELSE "" END AS SOLD_DEALER_CODE_Cognos) THEN "WON" ELSE "LOST" END AS WON_LOSS

What am I doing wrong here?

[Reeza]

Try removing as cognos_... variable portion of your nested CASE portion.

Another possible option within the statement may be IFC

[Patrick]

Not tested but I believe both case statements below would give you what you're after

  CASE 
    WHEN BD.BIDDER_DEALER_CODE = 
      (CASE 
        WHEN BD.VIN = S.VIN THEN S.Dlr_GMF_ID 
        ELSE "" 
        END 
      ) 
      THEN "WON" 
    ELSE "LOST" 
    END 
    AS WON_LOSS

  CASE 
    WHEN BD.VIN = S.VIN and BD.BIDDER_DEALER_CODE = S.Dlr_GMF_ID then "WON"
    when BD.VIN ne S.VIN and BD.BIDDER_DEALER_CODE=' ' then "WON"
    ELSE "LOST" 
    END 
    AS WON_LOSS

[PGStats]

Should be

CASE 
	WHEN BD.VIN = S.VIN THEN S.Dlr_GMF_ID 
	ELSE "" 
        END AS SOLD_DEALER_CODE_Cognos,
CASE 
	WHEN BD.BIDDER_DEALER_CODE = CALCULATED SOLD_DEALER_CODE_Cognos THEN "WON" 
	ELSE "LOST" 
        END AS WON_LOSS