Solved

Iterate thru a resultset and perform logic in DB2

Posted on 2007-03-27
1
436 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 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

737 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