Click here to Skip to main content
15,849,163 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
Select UsrMain.Name,UsrMain.DefaultSalesOrg,csslogicalunit.userlu ,
case when count(CssRplOutboundPackageInfo.Id)='' then 0 else count(CssRplOutboundPackageInfo.Id)  end as 
"Initial Package Count" 
from CssRplOutboundPackageInfo 
 left join UsrMain 
On UsrMain.Pkey=CssRplOutboundPackageInfo.UsrMainPKey       
left join csslogicalunit
on csslogicalunit.pkey = usrmain.csslogicalunitpkey
where CssRplOutboundPackageInfo.WorkState='Initial' 
and UsrMain.id= 'T89387'
group by UsrMain.Name,UsrMain.DefaultSalesOrg,csslogicalunit.userlu


The above query is not giving the desired result.

I want to print the Initial package count as zero when the count is zero.I am getting this result but when I try to print the columns like UsrMain.Name,UsrMain.DefaultSalesOrg,csslogicalunit.userlu,the Initial Package count is not coming zero i.e. it will not display any result.

My question is --how can I print these columns even when the count is zero.

What I have tried:

I tried to use declare command.But I am not getting the result..
Please answer it ASAP.
Posted
Updated 22-Mar-18 7:22am
v2
Comments
Naga Sindhura 27-Jan-17 5:53am    
UsrMain table is in LEFT JOIN. However you have used UsrMain.id= 'T89387' is in the where clause which means the code internally treat that as INNER JOIN/ JOIN and one reason is CssRplOutboundPackageInfo.WorkState='Initial' don't have any records if this is the case then you need rewrite the code to by marking CssRplOutboundPackageInfo in left join.

Use your UsrMain table as the main driver and LEFT OUTER JOIN to CssRplOutboundPackageInfo - that way you will always get a row returned (assuming User T89387 exists). Then the only issue is to deal with the number of Initial Packages being returned as NULL - you can use COALESCE for that as in Solution 1 - in this case I prefer ISNULL (It is marginally faster in this instance)
For example (note, untested as we don't know your schema)
select U.Name,U.DefaultSalesOrg,LU.userlu ,

count(ISNULL(CPI.Id,0)) as [Initial Package Count]

from UsrMain U
left OUTER join csslogicalunit LU on LU.pkey = U.csslogicalunitpkey
left OUTER join CssRplOutboundPackageInfo CPI On U.Pkey=CPI.UsrMainPKey AND CPI.WorkState='Initial' 
WHERE U.id= 'T89387'
group by U.Name,U.DefaultSalesOrg,LU.userlu
 
Share this answer
 
Hello:
Instead of using:
case when count(CssRplOutboundPackageInfo.Id)='' then 0 else count(CssRplOutboundPackageInfo.Id) end as
"Initial Package Count" 

try to use
COALESCE(COUNT(CssRplOutboundPackageInfo.Id),0) as "Initial Package Count"

In the case that COUNT() returns NULL, COALESCE returns 0 (the second argument)
 
Share this answer
 
Comments
Member 12965461 24-Jan-17 8:19am    
Thanks for the response.But my query is-when I try to print the desired coulmns the initial package count is not displaying the result as 0 when the count is 0.I want to print 0 as well as the columns.
Member 7870345 24-Jan-17 9:04am    
Hello:
Sorry but perhaps i dont understand the question then.
If you type the query (just you have written) as a "new query" in the "Sql Server Management Studio"? What do you get for the "Initial Package Count"
a) 0
b) NULL
c) an empty column?
Member 12965461 24-Jan-17 10:36am    
I am getting an empty column.
I want to print Initial package count as 0 when count is 0 as well as the columns
like Name,Defaultsalesorg,userlu.
Member 7870345 24-Jan-17 10:47am    
Could you send me the schema of the tables that appear on the query (that is, CssRplOutboundPackageInfo, UsrMain and csslogicalunit)?
Member 7870345 24-Jan-17 10:56am    
Sorry, i've forgot something else that i need to help you.
Could you send me the result (as text) of the query when is executed in the Sql Server Management Studio?
(please leave the headers in the text)
Thank you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900