Solved

Select with Nulls

Posted on 2011-02-25
8
167 Views
Last Modified: 2012-06-27
My attached code works fine as long as the @DealID exists in the table

But if it doesn't, columns are shown with empty data row.
 Screenprint
What I would like returned is the DealID and nulls like this
 Screenprint2
Declare @DealID int
Set @DealID = 1988

Select	IsNull(Substring(afsSource,4,4),0) dealID, 
		Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
		Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From	proc_cfa.dbo.P_AvailableForSale
Where	substring(afsSource,4,4) = @DealID
Group By substring(afsSource,4,4)
order by Cast(substring(afsSource,4,4) as Int)

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981508
Try this
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	IsNull(Substring(afsSource,4,4),0) dealID, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON IsNull(Substring(a.afsSource,4,4),0) = B.dealID

Open in new window

0
 
LVL 8

Expert Comment

by:rushShah
ID: 34981516
try this,
Declare @DealID int
Set @DealID = 1988

IF EXISTS(SELECT 1 FROM proc_cfa.dbo.P_AvailableForSale WHERE substring(afsSource,4,4) = @DealID)
BEGIN
Select	IsNull(Substring(afsSource,4,4),0) dealID, 
		Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
		Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From	proc_cfa.dbo.P_AvailableForSale
Where	substring(afsSource,4,4) = @DealID
Group By substring(afsSource,4,4)
order by Cast(substring(afsSource,4,4) as Int)
END
ELSE
SELECT @DealID, NULL AS Zeroes, NULL AS NonZeroes

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34981517
OR
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	afsSource, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON A.afsSource = B.afsSource

Open in new window

0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 34981536
Missed ORDER BY
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	afsSource, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON A.afsSource = B.afsSource
order by Cast(substring(a.afsSource,4,4) as Int)

Open in new window


SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	IsNull(Substring(afsSource,4,4),0) dealID, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON IsNull(Substring(a.afsSource,4,4),0) = B.dealID
order by Cast(substring(A.afsSource,4,4) as Int) 

Open in new window

0
 

Author Comment

by:lrbrister
ID: 34981647
Ok folks
rushShah's was first one to work...seems a little clunky but what do I know?  I'm the one asking for help.

Points will be awarded shortly with no objections?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34981799
like this use left outer join

Declare @DealID int
Set @DealID = 1988

Select      x.dealID
       ,Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,  
       ,Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From (select @dealid as dealid) as x
left outer join      proc_cfa.dbo.P_AvailableForSale
 on substring(afsSource,4,4) = x.DealID
Group By x.dealid
order by 1
0
 

Author Closing Comment

by:lrbrister
ID: 34999996
Thanks
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35000017
Glad I could help
Raj
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question