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
 

 

fetch the rows from database using cursor

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

New User


Joined: 14 Sep 2005
Posts: 54
Location: Chennai

PostPosted: Mon Nov 02, 2009 12:37 pm    Post subject: fetch the rows from database using cursor
Reply with quote

Hi,

I have to fetch the records from Database using cursor.
I have 4 fields in WHERE clause.
In that one field are optional, i.e. we may or may not receive the input value for that fields.

I need to fetch the record with 4 fields if all are provided in the input.
If 3 fields are provided(means 4th filed is not provided in the input) I need to use only 3 fields in the cursor to fetch the records form database.

How to achieve this?
Do I need to declare 2 cursors for it? Or any other simple method is there?

Thanks in adv

Sashi
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 02, 2009 1:35 pm    Post subject:
Reply with quote

if its a char_col then you can code a like
with all '%' when input is not given
with the inputvalue when given.

you can always code this (if 0 means no input ):
you can also use another flag-hv to indicate no-input.
Code:
where col1 = :hv1
   and col2 = :hv2
   and col3 = :hv3
   and (:hv4 = 0  or col4 = :hv)

But this has a very negative influence on performance (stage 2 and not indexable)

Optimal would be two cursors, but for maintainability, often one of the above is chosen.
Back to top
View user's profile Send private message
sashi

New User


Joined: 14 Sep 2005
Posts: 54
Location: Chennai

PostPosted: Mon Nov 02, 2009 2:57 pm    Post subject: Optimal would be two cursors, but for maintainability, often
Reply with quote

Hi Thanks for quick reply...

what about if i have 2 optional fields?
Do we need to declare 4 cursors?

Sashi
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 02, 2009 3:32 pm    Post subject:
Reply with quote

In theory : yes.

but practically : only if you have indexes available

if you don't have indexes, it is not that bad to code a stage 2 predicate with the (:hv=0 or col= :Hv).

Or you could start using Dynamic SQL or use REOPT(ALWAYS) , but that is something your DBA / coding standards guys have to decide.
Back to top
View user's profile Send private message
sashi

New User


Joined: 14 Sep 2005
Posts: 54
Location: Chennai

PostPosted: Mon Nov 02, 2009 5:48 pm    Post subject:
Reply with quote

Hi GuyC...

Thanks again for your valuable response
in your reply you have mentioned following..

where col1 = :hv1
and col2 = :hv2
and col3 = :hv3
and (:hv4 = 0 or col4 = :hv)

can you please explain clearly how to code this?

I have index on all fields..
when i coded same as above program complied successfully but I am not able to promote it to test region through change man...i am getting sqlcode=-401 error while promoting...

can any one please explain why??

Thanks
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Nov 02, 2009 6:03 pm    Post subject:
Reply with quote

i imagine hv4 is x-type.

suggest you change :hv4 = 0 to :hv4 = ' '

and if you have no idea about links for manuals, start at IBMs LookAt site

the results of entering -401 will list all DB2 error code manuals (each version), which you can then download.

also, at the top of this page is a 'download' button .

the first is a little ditty called MainFrameAbend Assistant.

suggest you download this to your PC.

Having not had a -401 in about 20 years,
I used my copy of Abend Assist,
which told me that I had a numeric/non-numeric problem.
Only place it could be is the compare.
So, without knowing anything about your table, it is just a guess.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Nov 02, 2009 7:38 pm    Post subject:
Reply with quote

another technique is using between and or like
like for char, between for dates and nums

suppose
hv3 is dec(4,0)
hv4 is char(4)

Code:
If hv3-input is given
    move hv3-input to hv3-low
    move hv3-input to hv3-high
else
    move 0      to hv3-low
    move 9999 to hv3-high
end-if
If hv4-input is given
    move hv4-input to hv4
else
    move '%%%%' to hv4
end-if

select ...
where ...
   and col3 between :hv3-low and :hv-high
   and col4 like :hv4

advantage is that between and like are stage1 and (last matching coll) indexable
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 compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 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


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