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
 

 

Need help on SQL Dynamic WHERE Clause

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

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Sat Jul 16, 2016 3:11 pm    Post subject: Need help on SQL Dynamic WHERE Clause
Reply with quote

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

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jul 18, 2016 1:43 pm    Post subject:
Reply with quote

What options have you thought of?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Jul 18, 2016 3:32 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jul 18, 2016 3:38 pm    Post subject:
Reply with quote

/* My viewpoint : As far as security is concerned there should be one unique field which should be made mandatory for search */
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Jul 18, 2016 3:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Jul 18, 2016 3:53 pm    Post subject:
Reply with quote

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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Jul 18, 2016 3:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Mon Jul 18, 2016 4:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Tue Jul 19, 2016 11:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Jul 20, 2016 12:03 am    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Jul 20, 2016 3:44 am    Post subject:
Reply with quote

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
View user's profile Send private message
prino

Active Member


Joined: 07 Feb 2009
Posts: 984
Location: Oostende, Belgium

PostPosted: Wed Jul 20, 2016 6:18 am    Post subject:
Reply with quote

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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Jul 21, 2016 12:27 am    Post subject:
Reply with quote

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
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 Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm
No new posts Need help with Oracle Dynamic SQL: Me... Anoop Chandran1984 COBOL Programming 1 Sat Feb 13, 2016 7:27 am
No new posts Build a dynamic query file hiravibk DFSORT/ICETOOL 2 Wed Feb 10, 2016 12:47 am


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