Embedded SQL with run time change for WHERE clause

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

Hi,

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.

Please suggest any way to code.

Thanks in advance.

Joseph Reynolds · Posted: Tue Oct 08, 2013 3:49 am

Hi,

It might be helpful to see an example or two of the predicates that would need to be built, including how the column(s) that can be SPACES or CHAR participate in the predicate.

Also, the initial set up of a dynamic query program is certainly higher than static but that is a one-time cost.

Is this an either-or scenario? If the variable(s) are SPACES then any value for the variable(s) is acceptable, otherwise the variable(s) must match the provided CHAR value?

Would this work, where C is the column/variable of interest and A is a companion variable that merely qualifies one predicate or the other?

If c=SPACES then
avalue=1
else
avalue=2
SQL SELECT ...
WHERE (:avalue=1) or (:avalue=2 and C = :cvalue)

mfguy01 · New User Joined: 06 Sep 2010 Posts: 30 Location: India

Hi Joseph,

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:-
EXEC SQL
SELECT COUNT(*) INTO WS-ABC FROM EMPTAB1
WHERE EMPNAME LIKE '%' :WS-EMP '%'
AND DEPT LIKE '%' :WS-DEPT '%'
AND JOINDATE ........
.......
END-EXEC.

Problem:-
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.

Joseph Reynolds · Posted: Tue Oct 08, 2013 9:18 pm

Hi,

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
(:useEmpName=-1))
AND ((:useJoinDate=1 AND JOINDATE = :joinDate) OR
(:useJoindate=0))
etc.

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.