RE: checking for null in a create view

Greg Hightshoe wrote: > Hello, > > I'm using Informix 7.2. Here is my problem. I am creating a view. In = my > CREATE VIEW statement, I am trying to concatanate strings to make a full> name. However, whenever I try to concatanate a null string with another > string, the returned string is null (e.g. If a first name is null and = the > last name is "Jones", the concatanation of the two returns a null, not > "Jones" (which is what I want)). I was wondering if there was a way = within > the select statement to check for the null and then perform an action = based > on whether or not the value is null. The suggestion to me has been to = use > a union, but I am hoping for a more elegant solution. Does anyone have = any > ideas? > > Thanks In Advance, > Greg Firstly, you cannot use UNION in a VIEW, as there are undefined = possibilities when another table is joined to that view. The only = alternative is to use SPL to concatenate the strings together, but that is = not going to be fast.... eg: SELECT str_name(col1, col2) full_name FROM tab1; Where the SPL str_name() performs the concatenation and NULL checking. = See the back of the SQL Tutorial for SPL syntax. HTH RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus Communications | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+