Solved

Select with Nulls

Posted on 2011-02-25
8
159 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now