Want IF to be true if ALL records meet criteria

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 2000 Forums

SQL Server Development (2000)

Want IF to be true if ALL records meet criteria

Author

Topic

Jimbo2
Starting Member

14 Posts

Posted - 2003-08-27 : 16:24:20

I'd like to create a query in which an IF statement will be true if ALL the records in a SELECT statement meet a criteria. In the following example the part in brackets is what I don't know how to code. Thanks in advance!

declare @count smallint

create table #test1
(case_nm smallint,
action_nm smallint)

insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (2)
insert into #test1 (case_nm, action_nm) values (2, 1)

set @count = 0
while @count < 2 begin
set @count = @count + 1
if [the action_nm is null for all records in the following select statement] (select action_nm from #test1 where case_nm = @count)
print 'all action numbers for case ' + cast(@count as varchar) + ' are null'
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 16:32:21

Here ya go:



SET NOCOUNT ON

declare @count smallint
declare @total smallint

create table #test1
(
  case_nm smallint NOT NULL,
  action_nm smallint NULL
)

insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (2)
insert into #test1 (case_nm, action_nm) values (2, 1)

set @count = 0
while @count < 2 
begin
    set @count = @count + 1
    
    select @total = COUNT(*) FROM #test1 WHERE case_nm = @count

    IF (select count(*) from #test1 where case_nm = @count and action_nm is null) = @total
      print 'all action numbers for case ' + cast(@count as varchar) + ' are null'
end

drop table #test1

Tara

Jimbo2
Starting Member

14 Posts

Posted - 2003-08-27 : 16:45:46

Thanks, but was hoping not to have to count the records twice.

Is there possiblity with the ALL operator, something like:
IF null = ALL (SELECT action_nm FROM #test1 WHERE case_nm = @count and action_nm is null)
Of course, this doesn't work, but was hoping for something along those lines.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 16:57:48

How about this:


SET NOCOUNT ON

declare @count smallint
declare @total smallint

create table #test1
(
  case_nm smallint NOT NULL,
  action_nm smallint NULL
)

insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (1)
insert into #test1 (case_nm) values (2)
insert into #test1 (case_nm, action_nm) values (2, 1)

set @count = 0
while @count < 2 
begin
    set @count = @count + 1
    
    select action_nm 
    into #test2
    from #test1 where case_nm = @count
    
    IF (select count(*) from #test2 where action_nm is not null) = 0
      print 'all action numbers for case ' + cast(@count as varchar) + ' are null'

    drop table #test2
end

drop table #test1

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-27 : 17:09:04

try to get away from loops and all that.

why not just:


select case_nm,
  sum(case when action_nm is null then 1 else 0 end) as Number_of_Nulls, 
  count(*) as Total
from
  #test
where
  case_nm < 2
group by 
  case_nm

Just check if Numer_Of_Nulls = Total. very easy if you use a derived table.

- Jeff

Jimbo2
Starting Member

14 Posts

Posted - 2003-08-28 : 08:23:03

Jeff, thanks for your reply. Sorry to be so dense, but I don't see how to get the print statement to execute multiple times using your solution. Any ideas?

-Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-28 : 09:11:27

why do you want a print statement? you are better off with "measureable" results via a query. also, you can take away the WHERE part (I just tried to copy your logic) and get results for all different case_nm's instantly:

This shows only the ones that match:

select 'All are null for ' + cast(case_nm as varchar) + '.' as result
FROM
(
select case_nm,
sum(case when action_nm is null then 1 else 0 end) as Number_of_Nulls,
count(*) as Total
from
#test
group by
case_nm
) A
WHERE Number_Of_Nulls = Total

this shows all, and whether they match or not:

select CASE WHEN Number_Of_Nulls = Total THEN 'All are null for ' ELSE 'All are NOT null for ' END + cast(case_nm as varchar) + '.' as result
FROM
(
select case_nm,
sum(case when action_nm is null then 1 else 0 end) as Number_of_Nulls,
count(*) as Total
from
#test
group by
case_nm
) A

But, again, SQL is NOT meant for "presenting" data or print statements like this. your presentation layer should do that. Focus on returning the DATA you need in SQL, and interpreting those results and notifying the users in whatever method you are using to access the SQL data.

i.e., if you are using ASP to access SQL Server, format he results and put a nice messages on the web page using ASP. If you are using a reporting tool like Crystal, do it in the report. In VB, put it on a form or in a msgbox, etc.

- Jeff

Jimbo2
Starting Member

14 Posts

Posted - 2003-08-28 : 09:54:45

Thanks for your help. My "real" program would not have print statements. In place of the print statement I was planning to have an update statement that would update another table with the case number whose action numbers were all null. But I believe it could best be done by modifying your solution with something like:

UPDATE case_file SET null_action_case = case_nm
followed by your select statement.

Actually I was working on something like this that seems simple but doesn't work yet!!
select case_nm
from
(select case_nm from #test1 where not exists (select case_nm from #test1 where action_nm is not null)) A group by case_nm

I do have this that works, but want to get away from loops:
while @count < 2 begin
@count = @count + 1
if not exists (select * from #test1 where case_nm = @count and action_nm is not null)
insert into case_file, etc (had used print statement in example for clarity)
end
The thing I like about this is the simplicity of the select statement -- no count() or case, just a simple where, but I just hate that darn loop.

Jimbo2
Starting Member

14 Posts

Posted - 2003-08-28 : 10:18:32

Hey guys! I think I have this, with much thanks to you!! Very simple, elegant...

insert into case_file (case_nm)
select case_nm
from
(select t1.case_nm
from #test1 t1
where not exists (select case_nm
from #test1 t2
where t2.action_nm is not null and t2.case_nm = t1.case_nm)
group by case_nm) A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources