How to set defaults on a join

Question

Post reply

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

Worked perfectly now with the defaults! Thanks a lot!