Query on SQL Dynamic WHERE Clause



IBM's flagship relational database management system

Query on SQL Dynamic WHERE Clause

Postby subratarec » Sun Jul 17, 2016 12:49 pm

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
subratarec
 
Posts: 3
Joined: Wed Dec 16, 2009 3:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Query on SQL Dynamic WHERE Clause

Postby BillyBoyo » Sun Jul 17, 2016 12:58 pm

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.

These users thanked the author BillyBoyo for the post:
subratarec (Sun Jul 17, 2016 1:00 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post