Still Query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Still Query issue

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-22 : 13:58:11

Hi

I have data like (this is only the sample, In fact there are 6000 lines)

order_number PONumber
1 NULL_Value
1 NULL_Value
2 AAA
2 NULL_Value
3 AAA
3 AAA

I would like to display only orders which have only NULL values.
So the results should be only order 1 because order two contants one 'NULL_value' and order 3 not contain.

Last time I got two replay :

SELECT Top(1) Order_Number
FROM Temp1
GROUP BY Order_Number
HAVING MIN(PONumber) IS NOT NULL

Declare @table table (order_number int, PONumber varchar(50))
insert into @table
SELECT 1, 'NULL_Value' union ALL
SELECT 1, 'NULL_Value' union ALL
SELECT 2, 'AAA' union ALL
SELECT 2, 'NULL_Value' union ALL
SELECT 3, 'AAA' union ALL
SELECT 3, 'AAA'

select * from @table a
where NOT EXISTS
(
SELECT * from @table b
where a.order_number = b.order_number
and PONumber not LIKE '%NULL_VALUE%'
)

However I still have issue

because One one order contains NULl_values and poNumber I do not want to any line from this order.
I would expect all orders which only have NULL ( 1 or 2 or 3 so on....)

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-22 : 14:13:30

select order_number
from @table
group by order_number
having max(case when ponumber <> 'NULL_VALUE' then 1 else 0 end) = 0

kwacz23
Starting Member

44 Posts

Posted - 2013-09-22 : 14:15:56

Thanks a lot for help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-23 : 01:21:13

Why do you store NULL_VALUE as a string. You can just NULL (without single quotes) and do

select order_number
from @table
group by order_number
having max(case when ponumber is not NULL then 1 else 0 end) = 0

Alternatively you can use

select order_number
from @table
group by order_number
having max(ponumber) is NULL and min(ponumber) is NULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources