|
View previous topic :: View next topic
|
| Author |
Message |
sashi
New User
Joined: 14 Sep 2005
Posts: 54
Location: Chennai
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
sashi
New User
Joined: 14 Sep 2005
Posts: 54
Location: Chennai
|
|
|
|
Hi Thanks for quick reply...
what about if i have 2 optional fields?
Do we need to declare 4 cursors?
Sashi |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
sashi
New User
Joined: 14 Sep 2005
Posts: 54
Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
