Page 1 of 1

Query on SQL Dynamic WHERE Clause

PostPosted: Sun Jul 17, 2016 12:49 pm
by subratarec
Hi All,

I have a query on SQL SELECT. I will try to describe the scenario as clear as possible from my end.

Scenario:
-------------------------------------------------------------------------------------
one CICS screen. In that screen earlier there was only one search criteria - "POLICY ISSUE DATE". When user will provide the date and hit enter it will LINK to another program where we have one SQL SELECT query with one WHERE clause (POLICY ISSUE DATE) and it will fetch the data.

But now we have included another 6 different criteria along with POLICY ISSUE DATE (so in total 7) and my client wants to give all flexibility to the user on choosing his/her search. Means User can fill one or two or all (any combination) and rest is same (Enter and it will LINK to subprogram)

Now as user can choose any combination of search from CICS Screen how should I go ahead and build dynamic WHERE clause (means if User has chosen one then SQL WHERE should work with that one if many then it should work with many)

It's a COBOL DB2 CICS program. There is only one DB2 table (MASTER_POLICY) Different filelds are listed below

POLICY START DATE
POLICY END DATE
POLICY HOLDER NAME
POLICY ID
COD
POLICY SETTLE START DATE
POLICY SETTLE END DATE


Earlier SQL:
--------------------------
SELECT * FROM <HL SCHEMA>.MASTER_POLICY  WHERE POLICY_START_DATE = <user given date>



But as there are many options now which approach should be the best/efficient to create an SQL SELECT which can have flexibile WHERE clause.

Could someone please guide me on this? Which approach should I take (if possible with small examples). Please let me know anymore info needed.

Thanks

Read more: http://ibmmainframes.com/viewtopic.php? ... z4EeEtiEWE

Re: Query on SQL Dynamic WHERE Clause

PostPosted: Sun Jul 17, 2016 12:58 pm
by BillyBoyo
The people answering are the same as where you originally asked. The weekend is a slow time. Our pagers don't go off when a new question arrives.

Topic locked.