How to set defaults on a join

• wing0t2

  SSC Enthusiast

  Points: 116

  More actions

  August 5, 2009 at 9:28 am

  #207813

  Hi everyone,

  I am trying to create a view with a left outer join. The following code works fine with creting the join, but I would like to know if it's possible for me to set default values for the columns from tableB? I've tried setting defaults in the underlying tableB value, but it doesn't seem to propagate into the view when it is created.

  create view myView

  as

  select tableA.col1, tableA.col2, tableB.col1, tableB.col2

  from tableA left outer join tableB

  on tableA.col1 and tableB.col1

  Is there anyway to set defaults for the rows that has no matches?

  Thank in advance for any help!

  Tom

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 5, 2009 at 9:40 am

  #1034904

  sure...the ISNULL or COALESCE function can do what you want:

  create view myView

  as

  select

  tableA.col1 as cola1,

  tableA.col2 as cola2,

  ISNULL(tableB.col1,'no selection') as colb1, --assuming a varchar or something

  ISNULL(tableB.col2,0) as colb2 --assuming an integer or something

  from tableA

  left outer join tableB

  on tableA.col1 = tableB.col1

  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!

• wing0t2

  SSC Enthusiast

  Points: 116

  More actions

  August 5, 2009 at 10:56 am

  #1034959

  Worked perfectly now with the defaults! Thanks a lot!