• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Select with Nulls

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
lrbrister
Asked:
lrbrister
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
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
 
rushShahCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rajkumar GsSoftware EngineerCommented:
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
 
lrbristerAuthor Commented:
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
 
LowfatspreadCommented:
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
 
lrbristerAuthor Commented:
Thanks
0
 
Rajkumar GsSoftware EngineerCommented:
Glad I could help
Raj
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now