Can i show defaults in view that joins two tables, for records that don't have matching record in 2nd table?

I have two tables, tblA and tblB. TblB has a FK to tblA, but not every record in tblA will have a corresponding record in tblB. In tblB, defaults are defined on certain columns.
If i create a view, joining tblA and tblB, i'd like to get the default values defined in tblB if there is no record in tblB. I know i can do a isNULL(tblB.fieldB1, 'the default value') in the view, but i'd have to change the value in the isNull statement if i ever gonna change the default value in tblB. So is there a way to show the default values in the view?
TO clarify, here some tablestructures.  I left out the types cause they are not relevant for the question.


fieldB1 default('def1')
fieldB2 default ('def2')

view as i'd like it to work:
select * from tblA left join tblB on tblA.A_ID = tblB.FID_tblA.

I know I can do this, but i'd like an alternative to that.
select A.A_ID, A.FieldA1, isNULL(B.fieldB1, 'def1') as fieldB1, etc

Hope the question makes sense.

LVL 11
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
there is no straight forward method, you can create a user defined function for this

CREATE FUNCTION dbo.retDefault (@TableName VARCHAR(100), @ColumnName VARCHAR(100) )
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)


select A.A_ID, A.FieldA1, isNULL(B.fieldB1, dbo.RetDefault('tblB','FieldB1') ) as fieldB1

dreadyAuthor Commented:
Excellent, thanks!!!
>there is no straight forward method, you can create a user defined function for this.  

That's for sure.  The function may have some uses, but I see too many issues to make it practical.  Not only are there datatype issues, but a function fails miserably when the default is an expression. Furthermore, the ISNULL approach is fundamentally flawed, because it reacts to a NULL column, not the missing row, which are not quite the same thing.

The only reasonable approach that comes to mind is to insert a dummy row in table b, do the join, then backout the insert.  Of course, that's a little beyond the means of a view, but could be done in a proc.

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

dreadyAuthor Commented:
Hey dqmq,

I know i allready closed the question... but i'd like to follow up on your comment about the Null solution. As far as i know, if i do the left join and there is no corresponding row in the joined table, all values are null... in which case would isNULL(tblB.fieldB1, 'test') not return 'test' if there is no row in tblB for that record?

Any comments would be appreciated!

>As far as i know, if i do the left join and there is no corresponding row in the joined table, all values are null

That's true; but what about the other way around: the row in tblB is present and fieldB1 is NULL.  For a solution, you must make the decision whether to substitute the default based on a non-nullable field in tblB.  Ususally, any column of the primary key is a good choice.   You can pass that column to the function and then use an 'IF @Req_Column IS NULL' condition inside the function.  Alternatively, you can make the test outside the function like this:

select A.A_ID, A.FieldA1, CASE WHEN B.FIELDBPK IS NULL THEN dbo.RetDefault('tblB','FieldB1') ELSE FieldB1 END as fieldB1

Other situations where the function will fail:
  1. when the default contains an expression, for example NULL, GETDATE(), or USER. I don't know any work-around for this problem.
  2. when there are multiple tables with the same tablename (in different schemas).  The function needs to qualify the table with a schema name, to avoid that possibility.


dreadyAuthor Commented:
Hey dqmq,

thanks so much for your info! The issues that you pointed out are understood, but will not affect us.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.