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
 

 

Embedded SQL with run time change for WHERE clause

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

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Mon Oct 07, 2013 6:01 pm    Post subject: Embedded SQL with run time change for WHERE clause
Reply with quote

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.
Back to top
View user's profile Send private message

Joseph Reynolds

New User


Joined: 07 Oct 2013
Posts: 6
Location: United States

PostPosted: Tue Oct 08, 2013 3:49 am    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
mfguy01

New User


Joined: 06 Sep 2010
Posts: 30
Location: India

PostPosted: Tue Oct 08, 2013 10:30 am    Post subject: Reply to: Embedded SQL with run time change for WHERE clause
Reply with quote

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.
Back to top
View user's profile Send private message
Joseph Reynolds

New User


Joined: 07 Oct 2013
Posts: 6
Location: United States

PostPosted: Tue Oct 08, 2013 9:18 pm    Post subject:
Reply with quote

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.
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 Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Fail to change physical VSAM filename... jacobdng CICS 7 Fri Jan 20, 2017 12:36 pm
No new posts CICS transaction slow response time vasanthz All Other Mainframe Topics 3 Thu Jan 19, 2017 1:31 am
No new posts DD DUMMY & CPU TIME Nileshkul Testing & Performance analysis 16 Mon Dec 19, 2016 11:35 pm


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