April 27, 2009 at 10:05 am
Hi everyone, I'm in the process of creating some tsql to do various lookups and selections on some order tables we have here. The current table I'm working against has the header and details in a single table (due to application design and other requirements). What I'm trying to do is work out the sql required for a selection of orders where the result column is Null for all order lines on that order. I'll give an example to help illustrate the problem.
OrderNo LineNo Product Result
1 1 ABC Null
1 2 XYZ Requires review
2 1 Foobar Null
2 2 Id10t Null
I need a script that can return all orders where all order lines for each OrderNo are Null in the Result column.
I thought I had it with using a CTE and a count distinct, however checking the results I can see orders in my list where there is a row that has Requires review in it.
Any guidance or help would be most appreciated.... Sorry I've been unable to product a table extraction, however the above should show a much simpler dataset.
April 27, 2009 at 11:05 am
How about something like this?
Declare @Table Table (OrderNo int, [LineNo] int, Product varchar(50), Result varchar(50))
Insert Into @Table
Select 1,1, 'ABC', NULL UNION ALL
Select 1,2, 'XYZ', 'Requires Review' UNION ALL
Select 2,1, 'FooBar', NULL UNION ALL
Select 2,2, 'ID10t', NULL
Select * from @Table
where OrderNo Not IN
(Select OrderNo From @Table Where Result IS NOT NULL)
April 27, 2009 at 11:50 am
Here is another method:
create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null
;
select * from #Orders;
with OrderResults(
OrderNum
) as (
select distinct
OrderNum
from
#Orders
where
Result is not null
)
select
o.OrderNum,
o.LineNum,
o.Product,
o.Result
from
#Orders o
left outer join OrderResults ors
on (o.OrderNum = ors.OrderNum)
where
ors.OrderNum is null;
drop table #Orders;
April 28, 2009 at 1:58 am
Hi Ken and Lynn
Thank you both for your responses. I'm kicking myself now over the answer, I can see clearly what you are doing with both of these methods, for the script I need to make I will be using Ken's method at this time, however the CTE method by Lynn may be of use for a future itteration of the application. Again thank you both for your time.
Regards
Timothy Merridew
April 30, 2009 at 4:13 pm
There is an easier way to do it. Using the COUNT(ColumnName) function counts only those rows that aren't null.
Here's an example:
create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null union all
select 3,1, 'ABC', 'Not Null' union all
select 3,2, 'DEF', 'Not Null'
;
-- Get the orders where all Result is null
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(Result) = 0
-- You can also get all of the orders that don't
-- have any nulls in Result
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(*) - COUNT(Result) = 0
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply