Fectch rows from table without using a cursor

Sriram K · New User Joined: 17 Feb 2009 Posts: 32 Location: Bangalore

Hi,

There are two tables A and B.

Table A:

Employee id name address company
-------------- ------- --------- -----------

Table B:

company Employee id
---------- ---------------

If table A contains 3 rows, I want to validate all the employee id's present in Table A are valid by checking their presence in table B.

I can do it by a cursor, by checking each and every records from A against Table B.

But without cursor i want to do it in a single select query. Is that possile.

If possible please help on that.

dbzTHEdinosauer · Posted: Fri Jul 10, 2009 3:31 pm

select count(*)
from tableA
where employee_id not in (select employee_id from tableB)

rajnikantvanpratiwar · New User Joined: 01 Jul 2009 Posts: 5 Location: Pune

Unload both tables into sequencial file and use as a input and you can do it thru file handling processing.

to crosscheck you can test using below query in DB2

select Employee id, name, address, company from Table A where Employee id IN (select Employee id from Table B);

above result gives all the Employee id's which are present in table B..

Sriram K · New User Joined: 17 Feb 2009 Posts: 32 Location: Bangalore

Hi,

I tried this particular query and it worked.

select employeed_id
from tableA
where employee_id not in (select employee_id from tableB)

But if the number of employee id's which is present in Table A and not in table B are more. How to track that. I mean, i need to report those missing entries. So how to collect it in a working storage varibale dynamically.

dbzTHEdinosauer · Posted: Fri Jul 10, 2009 4:30 pm

Sriram K · New User Joined: 17 Feb 2009 Posts: 32 Location: Bangalore

First i need to validate and then i need to process some set of records.

Both validating and processing the records are from same table.

So for validating i need to open,fetch,close the cursor.
Again for processing ( if valid) i need to open, fetch, close the cursor for same table.

So i thought for validating i can achieve using single select sattement. If valid then i can open cursor for processing.

dbzTHEdinosauer · Posted: Fri Jul 10, 2009 4:39 pm

I ask again, what is your cursor declaration?
you could validate with your WHERE clause.

what processing do you have to do?

and what file are you using? you keep talking about records,
which have nothing to do with DB2, it is ROWS.