dready
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
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.
ASKER
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
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
>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','Fie
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.
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','Fie
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.
ASKER
Hey dqmq,
thanks so much for your info! The issues that you pointed out are understood, but will not affect us.
dready
thanks so much for your info! The issues that you pointed out are understood, but will not affect us.
dready
ASKER