We help IT Professionals succeed at work.

SQL Concatonation in a SELECT not behaving as expected.

splanton
splanton used Ask the Experts™
on
I am concatonating some field values into a 'description' for an invoice. The only problem I can see is that the description appears empty when there are no InvoiceNotes.

Basically unless all the fields have values it appears to ignore the whole entry. ALL My WasteOrderDetail.JobRef rows have values but not all my WasteOrderDetail.InvoiceNotes rows will have.

The code I have is along these lines:

SELECT JobRef + ' - ' + ContainerSize.Description + ' ' + Container.Description + ' for ' + WasteType.Description +'. Notes: ' + WasteOrderDetail.InvoiceNotes as InvDescription
FROM WasteOrderDetail 
INNER JOIN Container ON WasteOrderDetail.ContainerId = Container.ContainerID
INNER JOIN ContainerSize ON WasteOrderDetail.ContainerSizeId = ContainerSize.ContainerSizeId
INNER JOIN WasteType ON WasteOrderDetail.WasteTypeId = WasteType.WasteTypeId 
WHERE ... etc

Open in new window


Is there any way round this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try || instead of + for concatenating.
SELECT JobRef  || ' - ' || ContainerSize.Description || ' ' || Container.Description || ' for ' ||   WasteType.Description || '. Notes: ' || WasteOrderDetail.InvoiceNotes as InvDescription
You need an outer join. In your case I think a right outer outer join but mayber a left outer join. This will include data from one table even if the other table has a null value. Just try left and right.

Also when concatenating a null value will cause the whole thing to be null. Use isnull() function on any field that might contain null values. Specify the field and then the replacement value. Usually for strings you replace with an empty string and numbers you replace with 0. So isnull(JobRef,'') + ...
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
@DOSLover:  || can be used with Oracle and some other DBMS, but not with MSSQL.

@splanton:
Grazhoppa is (partitially) right with both statements. An outer join is needed if there might be NO fitting records, but you didn't tell us if that is the case. How I read the question, rows are there, but values might be NULL. In that case your inner join works.

The behaviour of concating NULL to a string can be changed with
  SET CONCAT_NULL_YIELDS_NULL OFF
but it is much better to use the IsNull function as mentioned, in particular because of omitting delimiters for NULL values. E.g. if you use
   + isnull(ContainerSize.Description + ' ', '') +
the trailing space is only added if ContainerSize.Description is not null.
IsNull(column,'')

Author

Commented:
Great answers guys. Thanks again.

I found that || did not work :)

Then I used Grazzhoppa's solution and then found the update from Qlemo and used that instead. Both work very well. :)