In a Cobol-DB2 program, I need to write 200+ queries, in which WHERE clause values will be changed at run time. Suppose the variables in WHERE clause may contain either SPACES or certain CHAR value. In case of SPACES, all possible values will be consider for processing.
Now to implement it:-
1. I can write Dynamic query but that is not feasible considering complexities in queries like few queries are using JOINs, few are using SUB Queries etc.
2. I can't write 2 static queries considering high total count queries.
Thanks for response.
I checked for feasibility to use Dynamic queries but it doesn't seem possible to code in this scenario as each query is different from other and using atleast 3-4 tables to reach on count of output rows.
So far, I was using LIKE to reach on final count as it will work for both SPACES and CHAR values. Example:-
SELECT COUNT(*) INTO WS-ABC FROM EMPTAB1
WHERE EMPNAME LIKE '%' :WS-EMP '%'
AND DEPT LIKE '%' :WS-DEPT '%'
AND JOINDATE ........
1. Suppose we have Employee name as 'RAM' and 'RAMA' both then if we go to find count of rows where EMPNAME is RAM then using above query, it will give result considering both RAM and RAMA.
2. Suppose we want to check count of all employees on condition of JOINDATE, then values in EMPNAME and DEPT will have SPACES.
Please note that this is simplest way,i am trying to narrate the scenerio but in actual, we are using JOINs and SUB queries in each query.
Joined: 07 Oct 2013 Posts: 6 Location: United States
So it looks like you want to use a predicate clause conditionally - sometimes use it and sometimes do not. I think that the companion variable concept will work, but perhaps other methods exist. (My vote is still to build a dynamic query since it sounds like the qualifying predicates to use depend a great deal on the specific request. It might be hard to come up with a one-size-fits-all query.)
WHERE ((:useEmpName=0 AND EMPNAME LIKE '%':WS-EMP'%') OR
(:useEmpName=1 AND EMPNAME = :empName) OR
AND ((:useJoinDate=1 AND JOINDATE = :joinDate) OR
If you want to find rows where EMPNAME is RAM, then set useEmpName=1 and :empname='RAM'.
If you want to find all employees on condition of JOINDATE but don't care about EMPNAME then useJoinDate=1, JOINDATE = the desired date, useEmpName=-1.
You would need a useXXX variable/value for each predicate clause you want to selectively control.