Table Return Info If Null...

[tqeonline]

I have a Left Outer Join that goes and counts the number of rows where the column TC_Status = 'Passed'

LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT
          FROM TESTCYCL
          WHERE TC_STATUS = 'Passed'
          GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT
      ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC

However if I pass it a "RELEASE_CYCLES.RCYC_ID" that the table doesn't have then it joins "Null" to my existing table.

the RCYC_ID is just a number ex.1053
So im saying RCYC_ID = TC_ASSIGN_RCYC or 1053 = 1053

However if TC_ASSIGN_RCYC does not have a 1053 it returns NULL

What I need it to return is this:


(columns)TC_ASSIGN_RCYC CNT
(row) 1053 0

*I don't need the () values

So basically if the first table is null then return a table that "looks" like what it should have looked like if it counted 0.

 

[gmmastros]

try this:

LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT
          FROM TESTCYCL
          GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT
      ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC

This should return 0 counts *if* there is a matching row on TC_ASSIGN_RCYC even if there are not any rows with status = 'Passed'.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

Tried it... No go.

The issue is that there are no Rows in that table for '1053' so no Rows are returned.

So it is trying to find '1053' and it doesn't exist... so it returns NULL.

 

[tqeonline]

Is there a way to return a "made table" if the result of an ISNULL is null?

Ex. If (query) = Null then return "created table"

And that created table being:

TC_ASSIGN_RCYC CNT
1053 0

with the 1053 being the RELEASE_CYCLES.RCYC_ID

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

There are different ways to do this.

Ex. If (query) = Null then return "created table"

If Exists(Select 1 From Table where TC_ASSIGN_RCYC =1053)
  Begin
    -- Your existing select statement
  End
Else
  Begin
    Select 1053 As TC_ASSIGN_RCYC, 0 an CNT
  End

I won't necessarily say this is the best way to do things, but it probably most closely matches your pseudo-code.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

Here is the code I am trying to implement with your additions

If Exists(Select 1 From TESTCYCL where TC_ASSIGN_RCYC =1053)  
	Begin  
		SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT
        FROM TESTCYCL          
        GROUP BY TC_ASSIGN_RCYC
	End
Else  
	Begin    
		Select 1053 As TC_ASSIGN_RCYC, 0 As CNT  
	End

So do you think there is a better way to implement this?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

The thing that bothers me about this...

If the TESTCYCL table has rows in it where the TC_ASSIGN_RCYC has values other than 1053, the query will return that information. For example, suppose there are 100 rows in this table. Of those 100 rows, suppose there are 10 different TC_ASSIGN_RCYC values. The query (select ... from ... group by) query will return 10 rows. But... suppose none of those rows have 1053. In that case, you will get only one row with 1053, 0.

Most of the time, when people ask about showing 0 counts, they also want to show the other values (that are non-zero's). Often times, this is for a report.

For example, suppose you wanted to show the number of times each item from a restaurant's menu was ordered. So you have a table like this....

[tt]
OrderId FoodId
------- ------
1 1
2 1
3 2
4 2
5 2
6 3
[/tt]

The query would be:

[tt]
Select FoodId, Count(*) As OrderCount
From Orders
Group By FoodId
[/tt]

But... suppose nobody ordered the McLiver sandwich, but you still want to show that item with a 0 count, right?

The best way to accomplish this is by using another table that has each food item listed once.

[tt]
FoodId Name
------ ---------
1 Cheese Burger
2 Fries
3 Vanilla Milk Shake
4 McLiver Sandwich
[/tt]

Ideally, we would like to seen an output like this:

[tt]
Food Count
------------------ -----
Cheese Burger 2
Fries 3
Vanilla Milk Shake 1
McLiver Sandwich 0
[/tt]

To include the 0 counts, you need to first select from a table that has all of the items, and then left join to the other table. Like this:

-- Build a dummy table for the orders.
Declare @Orders Table(OrderId Int, FoodId Int)
Insert Into @Orders Values(1,1)
Insert Into @Orders Values(2,1)
Insert Into @Orders Values(3,2)
Insert Into @Orders Values(4,2)
Insert Into @Orders Values(5,2)
Insert Into @Orders Values(6,3)

-- Build a dummy table for the food items.
Declare @Food Table(FoodId Int, Name VarChar(100))
Insert Into @Food Values(1,'Cheese Burger')
Insert Into @Food Values(2,'Fries')
Insert Into @Food Values(3,'Vanilla Milk Shake')
Insert Into @Food Values(4,'McLiver Sandwich')

-- the query!
Select Food.FoodId,
       Food.Name,
       Count(Orders.OrderId)
From   @Food As Food
       Left Join @Orders As Orders
         On Food.FoodId = Orders.FoodId
Group BY Food.FoodId, Food.Name

The code shown above uses table variables to load the test data. You can copy/paste this code to a query window to see how it works.

In your situation, having a table that lists all of the TC_ASSIGN_RCYC just once will allow you to write a similar query.

Makes sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

Well I was going to post a big long thing but the site messed up and i'll give you a summary.

Basically I am doing a Left Outer Join:

LEFT OUTER JOIN (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT          FROM TESTCYCL          GROUP BY TC_ASSIGN_RCYC) AS CYCLE_PASSED_COUNT      ON RELEASE_CYCLES.RCYC_ID = CYCLE_PASSED_COUNT.TC_ASSIGN_RCYC

however this is used in a calculation:

(Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE)as Float) * .01) * (DAYS_SKEW.[ORDER] - Round(Cast(DATEDIFF(hh, RCYC_START_DATE, GetDate())as Float) / Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE)as Float)*100,0)) / 24 AS 'Days Ahead/Behind',

which uses DAYS_SKEW.[ORDER]

LEFT OUTER JOIN SQA_DASH_PROJECTED_CURVE AS DAYS_SKEW
   ON (SELECT TOP 1 POINT FROM SQA_DASH_PROJECTED_CURVE WHERE POINT <= (ROUND(CAST((CYCLE_PASSED_COUNT.CNT) as Float)/(CYCLE_SCRIPT_COUNT.CNT)* 100,0)) ORDER BY POINT DESC) = DAYS_SKEW.POINT

So IF you can follow that... then we are good to go.

The DAYS_SKEW says if it is <= CYCLE_PASSED_COUNT.CNT however that is being returned as NULL...

So if I could somehow say

IF THIS CODE IS NULL RETURN 0... that way it would say <= 0 which would return me Point = 0 and ORder = 1 (because i know the data)

(ROUND(CAST((CYCLE_PASSED_COUNT.CNT) as Float)/(CYCLE_SCRIPT_COUNT.CNT)* 100,0))

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

LEFT OUTER JOIN SQA_DASH_PROJECTED_CURVE AS DAYS_SKEW
      ON (SELECT TOP 1 POINT FROM SQA_DASH_PROJECTED_CURVE WHERE POINT <= 
      (isnull
      (
      ROUND(
      CAST(
      (CYCLE_PASSED_COUNT.CNT) as Float)
      /
      (CYCLE_SCRIPT_COUNT.CNT)* 100
      ,0)
      ,.147)
      ) ORDER BY POINT DESC) = DAYS_SKEW.POINT

is still returning me some nulls...??? I have it saying < .147 because that is the lowest value on the table. So it will return row 1.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

(Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE)as Float) * .01) * (isnull(DAYS_SKEW.[ORDER],1) - Round(Cast(DATEDIFF(hh, RCYC_START_DATE, GetDate())as Float) / Cast(DATEDIFF(hh, RCYC_START_DATE, RCYC_END_DATE)as Float)*100,0)) / 24 AS 'Days Ahead/Behind',

That's where the number is being calculated...

long story short i just put

(isnull(DAYS_SKEW.[ORDER],1)

ISNULL around that ... i hate simple solutions

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008