Solved

Iterate thru a resultset and perform logic in DB2

Posted on 2007-03-27
1
439 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] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 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