Sql query not giving the desired result

Question

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 24-Jan-17 1:13am

Member 12965461

Updated 22-Mar-18 6:22am

Jörgen Andersson

v2

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 7870345 · Answer 1 · 2017-01-24T01:30:00

Solution 1

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)

Posted 24-Jan-17 1:30am

Member 7870345

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.

CHill60 · Answer 2 · 2017-01-31T00:37:00

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