A field in your SELECT returns a NULL and thus the entire concatenated line is NULL.
Replacing "cast(Count(Number of Items))" by "cast(COALESCE(Count(Number of Items), 0) as varchar)" would do the trick ...
Good luck!
yah you're right, I forgot --> COUNT never returns NULL ... thanks for pointing this out 🙂
Back to Sangeeth878787's question, a blank line on the output of concatenated columns is typically due of a column returning NULL, so some column in the query must return NULL ...
The OP isn't getting a blank line, they aren't getting the line. I.e. They want to show a count (of 0) where data does not exist. This should be easily solved by changing the base table and the order of the JOINs.
As the OP hasn't fixed their SQL, I've left their errors in with my proposed solution (and copied them if I refer to the same field/table). I would like them to learn from their mistakes, and how to fix them.
SELECT '[ID- 1411]+ ',' + cast(Count(Number of Items))+ ',' + date.[Date Field] + ':'+ Tim[Half Hour Time Field] as ID
FROM dbo.dimdate dates on dates.DayDateKEY = i.[DayDateKEY]
CROSS APPLY dbo.DIMtime tim
LEFT JOIN dbo.FCTtable i ON dates.DayDateKEY = i.[DayDateKEY]
AND tim.DIMtimeKEY = i.[DIMtimeKEY]
LEFT JOIN dbo.DIMcountry contr ON contr.DIMcountryKEY = i.DIMCountryEY
AND contr.country = 'US'
where dates.DayDateKEY > '20160224070600' -- YYYYMMDDHHMMSS
group by date.DateField,
Tim[Half Hour Time Field];
Hi Thom,
As you said exactly, I want to show a count (of 0) where data does not exist. This should be easily solved by changing the base table and the order of the JOINs. But when I used Cross apply, the expected count is not same as using Left or inner joins. But output is correct when I use left or inner join but no line where data does not exist instead on Zero