?
Solved

Iterate thru a resultset and perform logic in DB2

Posted on 2007-03-27
1
Medium Priority
?
442 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:LuckyLucks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1500 total points
ID: 18806154
hi
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 ?
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question