Solved

Select with Nulls

Posted on 2011-02-25
8
163 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 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