Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us