I have a query on SQL SELECT. I will try to describe the scenario as clear as possible from my end.
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 SETTLE START DATE
POLICY SETTLE END DATE
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.
I have seen and worked on two approaches so far.
1.You need to come up with priority of these searches, for e.g if user enters policy number and name then it does not make sense as policy number is certainly unique so you will do a search with that and ignores the name. hence once you decide the search priority then you may have to write one wrapper cics module and 7 or 5 different sub module for each search field and do a XCTL to them from wrapper or driver module.
2. Create a dynamic sql table which will contains at least 2 columns one with unique Number and second with the sql only where clause. So create all your combinations and create those many rows in the table.same way write a wrapper which will decide the unique Number based on the search combination and XCTL a subroutine (which you will create with dynamic sql).
WHERE (:WS-POLICY-START-DATE > 0 AND
:WS-POLICY-END-DATE > 0 AND
:WS-POLICY-HOLDER-NAME > 0 AND
; <OTHER WORKING STORAGE VARIABLES>
POLICY_START_DATE = :WS-POLICY-START-DATE AND
POLICY_END_DATE = :WS-POLICY-END-DATE AND
POLICY_HOLDER_NAME = :WS-POLICY-HOLDER-NAME AND
; <OTHER VARIABLES >
(:WS-POLICY-START-DATE > 0 AND
POLICY_START_DATE = :WS-POLICY-START-DATE )
(:WS-POLICY-END-DATE > 0 AND
POLICY_END_DATE = :WS-POLICY-END-DATE)
Here I am trying to check if all the field variables are filled by user then do something and then I am issuing OR for if user fill only a particular field or combinations of fields..
I know this may not be the correct approach but if for time being I am continuing my coding and testing screens like this..
CSA are authorized. What if the customer who called doesn't know the policy number as that's most likely to happen? May be he needs more DOB ,and SSN ( last 4 digits) and then they ask security questions before even they discuss anything with you.
TS,I think you are new to cics. Remember it's not batch, do get checked with DBA's about the performance implications of using this quick untested and compiled code. I doubt any of you DBA will approve it. Have a design sessions with them before you move futher as then later you will anyways scrap your approach.
Yes you are correct. I suggested that if we can force customer use POLICY ID as mandatory field. Frankly I still haven't got any answer from them. So I am not very sure if they will go ahead with my request.
So for now could you please suggest what would be the easiest way to achieve the correct result in SELECT query? Which approach I should think of now..
Don't wonder and then get jumbled, First get your requirements clear and understand the need and then think of the approach, just because you can't put logic together doesn't mean you advise stakeholders about forcing policy id alone you will have to ask the reason for the expansion of the search criteria and don't act like drone.
CASE, I believe, works on the retrieved data. You are wanting to do stuff before retrieving the data so you will probably need EVALUATE (if using COBOL) or SELECT (if using PL/1) to generated the correct SELECT (SQL) statement.