I'm writing a T-SQL report that shows the number of accounts that are in different statuses for different customers. The report results in something like:
Customer1 NoService 7
Customer1 IncompleteOrder 13
Customer1 NULL 9
Customer2 NoService 12
Customer2 Available 19
Customer2 NULL 3
...
The 'NULL' status is valid data, but instead of displaying NULL, I want to display "Pending". Here is my SQL so far:
USE cdwCSP;
SELECT
sr.sales_region_name AS SalesRegion
, micv.value
, COUNT(sr.sales_region_name)
FROM prospect p
LEFT JOIN sales_region sr
ON p.salesRegionId = sr.sales_region_number
LEFT JOIN prospectOrder po
ON po.prospectId = p.prospectId
LEFT JOIN wo
ON wo.prospectId = p.prospectId
LEFT JOIN woTray wot
ON wot.woId = wo.woId
LEFT JOIN miscInformationCustomerCategory micc
ON micc.prospectId = p.prospectId
LEFT JOIN miscInformationCustomerValues micv
ON micv.miscInformationCustomerCategoryId = micc.miscInformationCustomerCategoryId
LEFT JOIN miscInformationCategory mic
ON micc.miscInformationCategoryId = mic.miscInformationCategoryId
WHERE wot.dateOut IS NULL
AND mic.categoryName LIKE '%Serviceability%'
GROUP BY sr.sales_region_name, micv.value
ORDER BY sr.sales_region_name, micv.value;
Any help would be appreciated, I'm still learning T-SQL so this might be an easy question to answer.
You can use COALESCE
or ISNULL
. The former is standard and returns the first NOT NULL
argument (or NULL
if all arguments are NULL
)
SELECT COALESCE(micv.value,'Pending') as value
ISNULL
is restricted to only 2 arguments but is more efficient in SQL Server if the first value to be tested is expensive to evaluate (e.g. a subquery).
One potential "gotcha" with ISNULL
to be aware of is that it returns the datatype of the first parameter so if the string to be substituted is longer than the column datatype would allow you will need a cast.
E.g.
CREATE TABLE T(C VARCHAR(3) NULL);
INSERT T VALUES (NULL);
SELECT ISNULL(C,'Unknown')
FROM T
Would return Unk
But ISNULL(CAST(C as VARCHAR(7)),'Unknown')
or COALESCE
would both work as desired.
you can also use ISNULL('value', 'replacewithvalue')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With