Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Iterate thru a resultset and perform logic in DB2

Posted on 2007-03-27
1
Medium Priority
?
444 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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 (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 you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

636 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