case when this field = abc then thisother field = 3

[evr72]

Hello,

I have a sql statment and I am using case when, this is working well, but I would like to add if field1='abc then filed2=3 and field3=3*fieldqty

here is what I have so far

SELECT
WEBQRY.SHPORD.FECHNB,
WEBQRY.SHPORD.FEKNNB,
WEBQRY.SHPORD.FEAAC3,
WEBQRY.SHPORD.FECVNB,
WEBQRY.SHPORD.FECBTX,
WEBQRY.SHPORD.FEAAC9,
WEBQRY.SHPORD.DDAITX,
WEBQRY.SHPORD.BIHJTX,
WEBQRY.SHPORD.DDARQT,
WEBQRY.SHPORD.DDAAGP,
WEBQRY.SHPORD.EXT_PRICE,
right(FEALDT, 6) AS Create_Date,
right(DDAIDT, 6) AS Promised_Date,
right(DHIVNB, 6) AS Shipment_Date,
DAVIESF.COMXCPT.ITNBR,
(case when WEBQRY.SHPORD.DDAITX ='7303' then WEBQRY.SHPORD.DDAAGP=3 and WEBQRY.SHPORD.EXT_PRICE=3*WEBQRY.SHPORD.DDARQT when WEBQRY.SHPORD.DDAITX = DAVIESF.COMXCPT.ITNBR then 1 when WEBQRY.SHPORD.DDAITX Between 'SW3000%' and 'SW3020%'  then 1 else 0 end) AS same
FROM WEBQRY.SHPORD
LEFT OUTER JOIN DAVIESF.COMXCPT ON WEBQRY.SHPORD.FECHNB = DAVIESF.COMXCPT.SLSNO AND WEBQRY.SHPORD.DDAITX = DAVIESF.COMXCPT.ITNBR AND WEBQRY.SHPORD.FEKNNB = DAVIESF.COMXCPT.CUSNO
WHERE ((case when WEBQRY.SHPORD.DDAITX ='7303' then WEBQRY.SHPORD.DDAAGP=3 and WEBQRY.SHPORD.EXT_PRICE=3*WEBQRY.SHPORD.DDARQT when WEBQRY.SHPORD.DDAITX = DAVIESF.COMXCPT.ITNBR then 1 when WEBQRY.SHPORD.DDAITX Between 'SW3000%' and 'SW3020%'  then 1 else 0 end) =0)

if i take this part out the code works well, not sure why this is not working

when WEBQRY.SHPORD.DDAITX ='7303' then WEBQRY.SHPORD.DDAAGP=3 and WEBQRY.SHPORD.EXT_PRICE=3*WEBQRY.SHPORD.DDARQT

any help is much appreciated!!! thanks!!!

 

[MarcLodge]

Hi evr72,

What are you expecting to be returned with the piece of code:

WEBQRY.SHPORD.DDAAGP=3 and WEBQRY.SHPORD.EXT_PRICE=3*WEBQRY.SHPORD.DDARQT

Are you expecting 2 values back?

The other WHEN clauses return one value so you need to examine exactly what it is you are trying to achieve and tailor the SQL accordingly. Get back to us if you require further help......

Marc

 

[blom0344]

The piece that you have to take out to make the SQL work represents a boolean expression , not a value. Since you evaluate the outcome of the case with a zero value you have a mismatch. I don't understand what you are trying to do in the offending part of the where clause in any case..

Ties Blom