Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fectch rows from table without using a cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sriram K

New User


Joined: 17 Feb 2009
Posts: 32
Location: Bangalore

PostPosted: Fri Jul 10, 2009 3:21 pm    Post subject: Fectch rows from table without using a cursor
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 10, 2009 3:31 pm    Post subject:
Reply with quote

select count(*)
from tableA
where employee_id not in (select employee_id from tableB)
Back to top
View user's profile Send private message
rajnikantvanpratiwar

New User


Joined: 01 Jul 2009
Posts: 5
Location: Pune

PostPosted: Fri Jul 10, 2009 3:32 pm    Post subject:
Reply with quote

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..
Back to top
View user's profile Send private message
Sriram K

New User


Joined: 17 Feb 2009
Posts: 32
Location: Bangalore

PostPosted: Fri Jul 10, 2009 4:22 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 10, 2009 4:30 pm    Post subject:
Reply with quote

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


so what is your cursor declaration?
Why not use a cursor if you know there will be more than one row in the results set?
Back to top
View user's profile Send private message
Sriram K

New User


Joined: 17 Feb 2009
Posts: 32
Location: Bangalore

PostPosted: Fri Jul 10, 2009 4:35 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jul 10, 2009 4:39 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us