create sql str with special condition

• armm1388

  SSC Eights!

  Points: 808

  More actions

  March 22, 2013 at 11:05 am

  #291229

  create sql str with special condition

  hellow

  I have 2 table

  Table a(code, price, check)

  Table b(code, percent)

  I want to create a sql string such below:

  Select a.code, a.price, (price * percent) as new_field

  From a,b

  Where a.code = b.code

  And this condition must be consider in sql str:

  If (check = 1)

  {

  New_field = price * percent

  }

  Else

  {

  New_field = price * percent * 8

  }

  Now how can put above condition in sql str?

  Thanks very much

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 22, 2013 at 11:09 am

  #1599897

  are you familiar with the CASE statement yet? it's what you need to add to your query to do what you are asking.

  Other suggestions: don't use the Old syntax Table1,Table2

  1. use the explicit INNER JOIN instead.

  2. don't name your columns with reserved word(percent,check)

  SELECT

  a.code,

  a.price,

  ( price * [percent] ) AS new_field,

  CASE

  WHEN [check] = 1

  THEN price * [percent]

  ELSE price * [percent] * 8

  END AS ReallyNewField

  FROM a

  INNER JOIN b

  ON a.code = b.code

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 22, 2013 at 11:12 am

  #1599901

  Try this:

  select

  a.code,

  a.price,

  new_field = a.price * b.percent * case when a.check = 1 then 8 else 1 end

  from

  a

  inner join b

  on (a.code = b.code);