Iterate thru a resultset and perform logic in DB2

Hi EE:

  How can I iterate thru a SQL query resultset and perform some logic on it.

For example, I have a resultset like below

name1 name2
Bob Smith
Ann Smitherson

I would like to iterate thru each row and check if
1. Select count(*) from mytable where name1='Bob'
2. Select count(*) from mytable where name2='Smith'
3. Select count(*) from mytable where name1='Ann'
4. Select count(*) from mytable where name2 ='Simtherson'

if the count in any one is 0 then I need the name of that column to be returned in my resultset

I cant use temp tables
Who is Participating?
momi_sabagConnect With a Mentor Commented:
how about doing it all in one sql and save yourself the trouble ?
select 'name1', name1
from  mytable
group by 'name1'
having count(*) = 0
union all
select 'name2', name2
from  mytable
group by 'name2', name2
having count(*) = 0

this query will return rows of the following type
'name1', 'bob'
'name2', 'smitherson'

that is for each value that contains 0 rows, you will get back the column name and the value that contains 0 rows

it seems odd that this is what you want to do,
i'll assume the actual names appear in a different table that is called name_table
in this case you will need to perform
select 'name1', t1.name1
from  mytable t1, names_table t2
where t1.name1 = t2.name1
group by 'name1', t1.name1
having count(*) = 0
union all
select 'name2', t1.name2
from  mytable t1 , names_table t2
where t1.name2 = t2.name2
group by 'name2', t2.name2
having count(*) = 0

this solves your problem ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.