May 11, 2011 at 1:03 pm
Greetings -
I think I may need "Coalesce" here but can't get my head around it. I have a query that needs to concantenate 3 fields in the output. To complicate it, I need to lookup the values to use from lookup tables and sometimes one of the fields may be null. So the code for this single field is like this:
(SELECT CT_County_Name FROM LookUps.dbo.CT_County_Table where CT_County = A.HA_COUNTY) + ', '
+ (SELECT TOP 1 ROUTE_NAME FROM LookUps.dbo.LKUP_County_Route WHERE LK_CODE = A.HA_ROUTE ) + ', '
+ CAST(A.HA_MILEPOST AS VARCHAR(5))
AS Location
of course, the problem there is that if any of the subqueries return NULL, the entire field becomes NULL.
Is there any way around this?
thanks in advance
May 11, 2011 at 1:12 pm
You can use IsNull on each field
IsNull((SELECT CT_County_Name FROM LookUps.dbo.CT_County_Table where CT_County = A.HA_COUNTY),'') + ', '
+ IsNUll((SELECT TOP 1 ROUTE_NAME FROM LookUps.dbo.LKUP_County_Route WHERE LK_CODE = A.HA_ROUTE ),'') + ', '
+ IsNull(CAST(A.HA_MILEPOST AS VARCHAR(5)) ,'')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 1:20 pm
Well, dog poo... that's what I had been trying but couldn't get the parser to like it. Maybe I still had my parathesis messed up 'cause it seems to like it the way you wrote it.
thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply