Concat nulls in view

[DaveRook]

Hi all,

I have a view which is used to list and amalgamate all my products. This makes a string search on a website more efficient than searching each table seperately (I think!)

However, there are no links between the 4 tables (all are seperate products) so to join the tables, I use something like: join where books.ID <> dvd.ID <> games.ID <> cd.ID

This gives me a view which lists all of my products which is what I want. The problem is the alias's. I now have 4 columns: booksID, dvdID, gamesID and cdID. So, I concatate them: (b.ID + d.ID + g.ID + c.ID) AS ConcatID. This works.

Some of the ID's can be left blank (null). Don't ask why, they just can! A perfect example of why we don't use the term ID!! Any way, this means all my ContactID fields return null.

I know I can use SET CONCAT_NULL_YIE LDS_NULL ON but this doesn't appear to work in a view. I get told off SQL can't create it in the diagram.

Any ideas?

[ck9663]

Yes.

Use ISNULL() function on your concatenated expression.

Good Luck!!!

~~ CK

[DaveRook]

Thanks, but that will won't work. It appears when the fields are being added together they return null because at least 1 item is of value null. So, to do a ISNULL() will only replace the current null value with something else.

May be I'm just being lazy and should use the alias's to create 4 seperate columns in the view and then get the website to check all the columns; it just seemed to be a good way to save time by doing it in SQL

[ck9663]

In this expresssion:

(b.ID + d.ID + g.ID + c.ID) AS ConcatID

Do you want it to return NULL or do you want it to return the values that are not NULL and just replace those NULL with blank. ie, B GC means your second ID is NULL? If that's the case, use ISNULL on each of those columns.

Happy Coding!!!

~~ CK

[DaveRook]

Ah, thank you. I tried to do the entire thing which of course won't work... Seems so obvious now it's been pointed out. Thank you for your help and the time, this has worked perfectly. Thank you