This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".
I expect I'd have the same problem with nulls even if I wasn't using subqueries.
SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldXTheISNULL function should help you out:
SELECT 'f1='+ISNULL(CAST(COUNT(subquery-1) AS VARCHAR),'')+', f2='+ISNULL(CAST(COUNT(subquery-2) AS VARCHAR),'') AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldX
No comments:
Post a Comment