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
 

 

cursor based on input values

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

New User


Joined: 21 Nov 2006
Posts: 6
Location: chennai

PostPosted: Mon Mar 17, 2008 10:42 pm    Post subject: cursor based on input values
Reply with quote

Hi,

I have to declare a cursor in DB2 stored procedure which select values from a table based on 5 conditions. according to my requirement, i get 5 values from input. But if one or more input values are not passed( ie blank) the corresponding condition can be ignored.

Declare <cursor name> Cursor for
Select fld1,fld2 from <table name>
where
fld1 = :inp-fld1 and
fld2 = :inp-fld2 and
fld3 = :inp-fld3 and
fld4 = :inp-fld4 and
fld5 = :inp-fld5

but if inp-fld5 is blank, last condition should be ignored (ie all values should be fetched without considering fld5 condition)

Declare <cursor name> Cursor for
Select fld1,fld2 from <table name>
where
fld1 = :inp-fld1 and
fld2 = :inp-fld2 and
fld3 = :inp-fld3 and
fld4 = :inp-fld4


Can a cursor be declared based on the input values. Please let tell me your suggesstion.

Thanking you in anticipation
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Mon Mar 17, 2008 10:59 pm    Post subject:
Reply with quote

Solution:

You can use Between clause for the optional column.

Declare <cursor name> Cursor for
Select fld1,fld2 from <table name>
where
fld1 = :inp-fld1 and
fld2 = :inp-fld2 and
fld3 = :inp-fld3 and
fld4 = :inp-fld4 and
fld5 between :inp-fld5-min and :inp-fld5-max


Check the input field,

if the input is greater that Zero (in case of Numeric) and greater than spaces (incase of alphanumeric), move the input value to both Min and Max variable.
move <input value> to inp-fld5-min
move <input value> to inp-fld5-max
else
If the fld5 is numeric then you can
move 0 to inp-fld5-min and
move all 9 to inp-fld5-max

If it is alphanumeric
then Move Low-values to inp-fld5-min
and Move High-values to inp-fld5-max.

Hope this would help icon_smile.gif
Back to top
View user's profile Send private message
kalarani

New User


Joined: 21 Nov 2006
Posts: 6
Location: chennai

PostPosted: Mon Mar 17, 2008 11:16 pm    Post subject:
Reply with quote

thanks a lot..
I hope this will work for my code..
thank you
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Mar 17, 2008 11:21 pm    Post subject:
Reply with quote

I don't think you can call a stored procedure without all the parameters. For the example you are showing just set the value of a missing parameters to the same value as a one of the parameters.
Back to top
View user's profile Send private message
kalarani

New User


Joined: 21 Nov 2006
Posts: 6
Location: chennai

PostPosted: Tue Mar 18, 2008 11:22 pm    Post subject:
Reply with quote

Actually i have 3 mandatory input fields apart from these 5 conditions. But according to min and max values check, I have another criteria that the field is checked with set of input values ie
fld4 in ( :inp-var1, :inp-var2, inp-var3)

so if corresponding value for fld4 is blank. how can i specify min or max value ( actually it is an alphanumeric field).
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Mar 18, 2008 11:43 pm    Post subject:
Reply with quote

In this case, I would suggest to have 2 queries one with Min, Max check and another with IN Clause against the input value.

Check the Input value, If it is greater than spaces
then Open the cursor which has IN clause

If it is spaces,
then Please use this Min and Max.
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Mar 19, 2008 11:50 am    Post subject:
Reply with quote

Hi kalarani,
The below code also will make you simple. Please check it out .

Check the inp-fld5 as follows.
If inp-fld5 > spaces
Move 'N' to ws-input-flag
ELSE
Move 'Y' to ws-input-flag
END-IF

Note : If the field is numeric then check respectively

Code:

Declare <cursor name> Cursor for
Select fld1,fld2 from <table name>
where
fld1 = :inp-fld1 and
fld2 = :inp-fld2 and
fld3 = :inp-fld3 and
fld4 = :inp-fld4 and
( fld5 = :inp-fld5 OR :ws-input-flag= 'N')
[/code]
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


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