but i have data for few fields in the WHERE CLAUSE bcz source pgms call this subroutine and pass the values into WHERE CLAUSE of some fields,not for all all fields.
Here i want to control the data in the where cluse.If the where clause field is having defaut vales(numeric field is zeros and non numeric is specess) then that field should not be appear in the where cluse of query.The where clause should be contain only variables which contain data.
My main concern is,In the above dynemic sql cursor i am passing the data for 7 fields so the where clause also should be 7 fields only.for that how we can skip if the variable contain default values then skip from the where cluse and if the variable contain data then append that variable in to where clause of query.How we can do this logic through string handling functions.
I guess that the forum is the right one...
the issue is not, strictly speaking, db2 related,
it' s rather a generic issue of building a string skipping some substrings on some conditions
the main issue is how to identify the condition for skipping the substring
code snippet ( just the logic, check the syntax, the commas and the apost' s)
I hope that the COBOLESE purists will forgive me
..... initialize the string
'SELECT * FROM ARW0.CNTL_TBE’,
DELIMITED BY SIZE
.... insert the substring depending on some condition
IF SEND_RT_NO has been passed
'WHERE SEND_RT_NO ......... '
DELIMITED BY SIZE
.... repeat for all the parameters
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
You are basically talking implementing logic rather than a simple/common string statement.
You would not code a single statement string to selectivedly include/exclude pieces. You would either write some code that works from "top down" and appends the needed components or code like Enrico suggests that will allow you to iterate thru the components appending those that are needed for this query execution.