INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

case when this field = abc then thisother field = 3

case when this field = abc then thisother field = 3

case when this field = abc then thisother field = 3

(OP)
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

CODE

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

CODE

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

RE: case when this field = abc then thisother field = 3

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

RE: case when this field = abc then thisother field = 3

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close