I am trying to combine 2 fields in a SQL statement as follows:

Select Field1 + Field2 AS tablefields from table

However when Field2 is Null it makes the column tablefields null regardless of whether Field1 has data or not. Is there a way to avoid this?

Note*
The real SQL statement is a series of LEFT OUTER JOINS combining fields from different tables into one field, but if one of the fields is Null it makes the entire end result for that column Null.

Travis

Hello Travis,

every operation with NULL comes to NULL

a + NULL = NULL
a || NULL = NULL and so on and so on

What you are seeking for is DECODE.

Try your statement with the following commands


Select Field1 || DECODE(Field2, NULL, '', Field2) AS tablefields from table


I hope that you are using Oracle otherwise found the counterpart to DECODE ...

Hope this helps ?

Regards

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

In this instance I am using SQL Server 2000 which doesn't have DECODE. I figured it out though, have to use the CASE Function like such:

Select (CASE WHEN Field1 IS NULL THEN '' ELSE Field1) +
(CASE WHEN Field2 IS NULL THEN '' ELSE Field2 END) As bothfields
From Table

Thanks for the lead.
Am I posting this on the write Forum? Is this only for Oracle/PSQL?

Travis

Hello Travis,

it is Ok to post SQL questions into this forum, but it is very helpful, when you give a short hint about the database you use. If the question is to specialized, I will move the thread to the correct forum and send an email to the poster

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com