[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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.

tblA:
A_ID
fieldA1

tblB:
FID_tblA
fieldB1 default('def1')
fieldB2 default ('def2')
fieldB3
fieldBN

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.

Dready
0
dready
Asked:
dready
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase 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) )
RETURNS VARCHAR(100)
AS
BEGIN
      RETURN(SELECT REPLACE(REPLACE(COLUMN_DEFAULT, '(''','') ,''')','') FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
      
END

GO


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


0
 
dreadyAuthor Commented:
Excellent, thanks!!!
0
 
dqmqCommented:
>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.


0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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!

Dready
0
 
dqmqCommented:
>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.


 

   
0
 
dreadyAuthor Commented:
Hey dqmq,

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

dready
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now