conditional statement to use for export query

• jmj

  SSC Enthusiast

  Points: 122

  More actions

  March 19, 2007 at 12:15 pm

  #369820

  I need to write a statement that I can schedule as an export. 

  I'm having a bit of a time with the if/then statement.

  I need all the clients out of the client table.

  If the client has a location_code of '01','02','03','04' in the client table

  then I need to pull the following fields from the client table

  SELECT FN, LN, Address, Counselor

  FROM Client

  If the client has a location_code of '05','06','07','08','09','10' in the client table

  then I need to pull the following fields from the client table

  SELECT FN, LN, Address, Advisor

  FROM Client

  I need them to come out on one list.  I'm thinking it should be an easy if/then statement, but I keep getting errors.

  Thanks for any ideas.

  J

   

• Sean May-415583

  SSC Enthusiast

  Points: 144

  More actions

  March 19, 2007 at 12:54 pm

  #695786

  There's not much of an if/then since you want "if/both".   Assuming the datatypes of your Counselor and Advisor fields are manageable, you can use this...

  SELECT FN, LN, Address, Counselor

  FROM client

  WHERE location_Code in ('01','02','03','04')

  UNION ALL

  SELECT FN, LN, Address, Advisor

  FROM client

  WHERE location_Code in ('05','06','07','08','09','10')

  If the Counselor and Advisor fields aren't already identical data types in the client table (sounds like they're both holding names, so we can hope?), then you might need to substitue 

  "...

  UNION ALL

  SELECT FN, LN, Address, Advisor..."

    with

  "...

  UNION ALL

  SELECT FN, LN, Address, CAST(Advisor as <CounselorDataType&gt..."