|
View previous topic :: View next topic
|
| Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
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
| Code: |
POLICY START DATE
POLICY END DATE
POLICY HOLDER NAME
POLICY ID
COD
POLICY SETTLE START DATE
POLICY SETTLE END DATE |
Earlier SQL:
--------------------------
| Code: |
| 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 |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| What options have you thought of? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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). |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| /* My viewpoint : As far as security is concerned there should be one unique field which should be made mandatory for search */ |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi Pandora,
For time being I am using below..
| Code: |
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 >
;)
OR
(:WS-POLICY-START-DATE > 0 AND
POLICY_START_DATE = :WS-POLICY-START-DATE )
OR
(:WS-POLICY-END-DATE > 0 AND
POLICY_END_DATE = :WS-POLICY-END-DATE)
OR
REMAINING CODES....
|
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..
Thanks |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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. |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi All,
Just one more info in our table we have only POLICY ID as unique value all others can have dups.
Thanks |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi Pandora,
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..
Thanks |
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi,
Just wondering if anyway I can use SQL CASE stmt to resolve the issue. plz let me know if anyone is aware of it..
Thanks |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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. |
|
| Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
That's why there is dynamic SQL!
And how you translate the PL/I code posted here to that sissy language you're using, that's up to you:
| Code: |
1/**********************************************************************
* XXXX - Working storage *
**********************************************************************/
DCL 1 XXXX_WORK,
2 EYE_CATCH06 CHAR (16) INIT ('XXXX WORK06'),
2 DYNAM CHAR (8192) VAR,
2 EYE_CATCH99 CHAR (16) INIT ('XXXX WORK99');
1/**********************************************************************
* XXXX - DB2 cursors *
**********************************************************************/
EXEC SQL
DECLARE SUMMARY CURSOR FOR XXXX_1_STMT;
/**********************************************************************
* XXXX - Retrieve data from database (or not) *
**********************************************************************/
CALL BUILD_XXXX_QUEUES();
1/**********************************************************************
* BUILD_XXXX_QUEUES: *
* *
* This procedure builds a temporary storage queue with the data *
* retrieved from the database. *
* *
* If XXXX is entered through non-standard means, ie not from the *
* normal selection screen, the calling program should *
* *
* - set the non_edx indicator to '1'b *
* - store the required UMR & TR in the pass fields in the comarea *
**********************************************************************/
BUILD_XXXX_QUEUES: PROC;
DYNAM = 'SELECT ' ||
'P1.RISK_UMR, ' ||
'P1.TRAN_REF, ' ||
'P1.TRAD_PTR_ID, ' ||
'CLSG_SET_CCY, ' ||
'RSKT_NET_PSTG_AMT, ' ||
'CLSG_SET_DTED, ' ||
'PAY_STUS, ' ||
'FST_TRANSM_DTE, ' ||
'TRANSM_DTE, ' ||
'TBK_STUS, ' ||
'TBK_DTE, ' ||
'LIMRES_STUS, ' ||
'LIMRES_DTE, ' ||
'ACCT_NO, ' ||
'TTRN_CODE, ' ||
'MSG_FWDCHN_UMR,' ||
'IPOS_SND_PAY_IND, ' ||
'IPOS_SPSET_IND, ' ||
'MSG_PAY_OS_IND, ' ||
'PST_ACCEPT_REF, ' ||
'IPOS_SET_DTED, ' ||
'P1.UPD_CNT, ' ||
'M1.UPD_CNT, ' ||
'MSG_TIME_STAMP, ' ||
'MOD_UW_REF, ' ||
'RISK_DIVN_IDENT, ' ||
'TYR_CODE, ' ||
'RISK_ROOT_NO, ' ||
'PRM_RISK_SUFFIX, ' ||
'PRM_TRANS_SEQ, ' ||
'IPOS_NO, ' ||
'TRACKING_IND ';
DYNAM = DYNAM ||
'FROM ' ||
SQLID || 'VPYPOST P1, ' ||
SQLID || 'VPYMSG M1 ';
DYNAM = DYNAM ||
'WHERE ' ||
'P1.RISK_UMR = M1.RISK_UMR AND ' ||
'P1.TRAN_REF = M1.TRAN_REF AND ' ||
'TOPR_LEGAL_ENT = ''' || XXXX_WORK.TOPR_LEGAL_ENT || ''' ';
IF COM_DATA.TRAD_PTR_ID ^= '' THEN
DYNAM = DYNAM ||
'AND P1.TRAD_PTR_ID = ''' || COM_DATA.TRAD_PTR_ID || ''' ';
SELECT;
WHEN (COM_DATA.POSTCODE = ' ');
WHEN (INDEX(COM_DATA.POSTCODE, ' ') ^= 0 |
INDEX(COM_DATA.POSTCODE, '%') ^= 0 |
INDEX(COM_DATA.POSTCODE, '_') ^= 0)
DO;
DYNAM = DYNAM ||
'AND TOPR_DIVCODE = ''' ||
SUBSTR(COM_DATA.POSTCODE, 1, 2) || ''' ';
IF SUBSTR(COM_DATA.POSTCODE, 3, 3) ^= ' ' THEN
DO;
DYNAM = DYNAM ||
'AND TOPR_CODE LIKE ''';
DO #I = 3 TO LENGTH(COM_DATA.POSTCODE);
SELECT (SUBSTR(COM_DATA.POSTCODE, #I, 1));
WHEN (' ') DYNAM = DYNAM || '_';
OTHER DYNAM = DYNAM ||
SUBSTR(COM_DATA.POSTCODE, #I, 1);
END;
END;
DYNAM = DYNAM || ''' ';
END;
END;
OTHER
DYNAM = DYNAM ||
'AND TOPR_DIVCODE || TOPR_CODE = ''' ||
COM_DATA.POSTCODE || ''' ';
END;
SELECT;
WHEN (NON_EDX |
LTSE_TREATY);
WHEN (FROM_CLOSURE)
DO;
DYNAM = DYNAM ||
'AND CLSG_SET_CCY = ''' || CLSG_SET_ISO || ''' ';
DYNAM = DYNAM ||
'AND CLSG_SET_DTED = ''' ||
SUBSTR(COM_DATA.CLSG_SET_DTED, 1, 2) || '.' ||
SUBSTR(COM_DATA.CLSG_SET_DTED, 3, 2) || '.' ||
SUBSTR(COM_DATA.CLSG_SET_DTED, 5, 4) || ''' ';
DYNAM = DYNAM ||
'AND CLSG_UW_ACCT_REF = ''' ||
COM_DATA.UW_ACCT_REF || ''' ';
END;
WHEN (COM_DATA.HELD_ITEMS ^= '')
DO;
DYNAM = DYNAM ||
'AND PAY_STUS = ''H'' ';
CALL ADD_DATES('FST_TRANSM_DTE');
END;
WHEN (COM_DATA.OVERNIGHT ^= '')
DYNAM = DYNAM ||
'AND PAY_STUS = ''S'' ';
OTHER
CALL FULL_SELECT();
END;
DYNAM = DYNAM ||
'AND M1.MSG_TIME_STAMP = ' ||
'(SELECT MAX(MSG_TIME_STAMP) ' ||
'FROM ' ||
SQLID || 'VPYMSG MC ' ||
'WHERE ' ||
'P1.RISK_UMR = MC.RISK_UMR ' ||
'AND P1.TRAN_REF = MC.TRAN_REF) ';
DYNAM = DYNAM ||
'ORDER BY ' ||
'RISK_DIVN_IDENT, ' ||
'TYR_CODE, ' ||
'RISK_ROOT_NO, ' ||
'PRM_RISK_SUFFIX, ' ||
'PRM_TRANS_SEQ, ' ||
'IPOS_NO, ' ||
'P1.TRAN_REF';
EXEC SQL
PREPARE
XXXX_1_STMT
FROM
:DYNAM;
$$NON_00000;
EXEC SQL
OPEN SUMMARY;
$$NON_00000;
CALL FETCH_SUMMARY();
DO WHILE(SQLSTATE = '00000');
DATA_FOUND = '1'B;
CALL STORE_DATA_IN_QUEUE();
CALL FETCH_SUMMARY();
END;
$$NON_02000;
EXEC SQL
CLOSE SUMMARY;
$$NON_00000;
|
|
|
| Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007
Posts: 126
Location: Bangalore
|
|
|
|
Hi Prino,
Thanks!! a lot for spending time on my query and providing me the complete solution. I haven't gone through completely but I am reading now. If I have any further query I will post here.Thanks!. |
|
| Back to top |
|
|
|
|
