if else statement

• paul.starr

  SSCrazy

  Points: 2505

  More actions

  November 14, 2008 at 11:04 am

  #193796

  whats wrong with this statement

  how would I write this statement using CASE Function

  "If AddressTypeID = 1 Then

  Trim(fields(""Field11""))

  ElseIf AddressTypeID = 4 Then

  Null"

  here is my statement:

  CASE WHEN C.AddressTypeID = '1' THEN A.State IF ELSE C.AddressTypeID = '4' THEN 'null' END

  from

  dbo.CustomerAddresses C

  JOIN dbo.AVW_11i_WA_ADDRESS A

  on C.CustomerID = A.Account_Number

  says there is an error near "if", am i missing a comma or something.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 14, 2008 at 11:21 am

  #898778

  http://msdn.microsoft.com/en-us/library/ms130214(SQL.90).aspx

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 14, 2008 at 4:23 pm

  #898925

  Hey Paul

  As a general reminder, remember that CASE is an expression, much like a function. It returns a value. Your error is that you can't mix an IF statement into a CASE expression.

  The form of CASE expression that looks most similar to what you are doing involves a series of WHEN clauses, finished off with an ELSE ... END, like this:

  CASE WHEN C.AddressTypeID = '1' THEN A.State

  WHEN C.AddressTypeID = '4' THEN 'null'

  ELSE null -- unanticipated result

  END

  The above statement can handle all types of comparisons on one or more columns or variables.

  If you are just testing a column or variable for equality, you can rewrite this as follows:

  CASE C.AddressTypeID

  WHEN '1' then A.State

  WHEN '4' then 'null'

  ELSE null

  END

  By the way, in your comparison to '4' I noticed you are returning a constant value of 'NULL'. If you aren't already aware, 'NULL' is not the same thing as NULL.

  Bob

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills