IBM Business Analytics

 View Only
Expand all | Collapse all

Using macro join function to create multiple in statements

  • 1.  Using macro join function to create multiple in statements

    Posted Wed March 13, 2024 05:09 AM

    Hi,

    I'd like to ask if someone knows how to create a macro in a query filter that results in multiple in statement e.g. LCG_TYPE in ('a') or LCG_TYPE in ('b') or LCG_TYPE in ('c') based on user's input. I used this technique due to Oracle limit on 5000 values  for in statement.

    As  you may see below, I used join, split and promptmany macros which returns up to a point what I needed but I cannot find the way to set  closing  parenthesis ")" at the end of each in clause.

    #join(' or 'substitute('^","[LCG_TYPE] IN 'split(':',promptmany('psearchValue', 'string','1'))))#

    Looking forward for any suggestion that you may have.

    Kind Regards,

    Pantelis



    ------------------------------
    Pantelis Stavroulidakis
    ------------------------------


  • 2.  RE: Using macro join function to create multiple in statements

    IBM Champion
    Posted Mon March 25, 2024 11:00 AM

    Hi Pantelis,

    With promptmany macro function, the last parameter is TextFollowing (This optional parameter is the closing parenthesis that is used most often for the promptmany function. This parameter is also useful when the prompt is optional and is followed by hardcoded filters in the SQL statement.)

    More information in: https://www.ibm.com/docs/en/SSEP7J_11.2.0/pdf/ug_fm.pdf (pdf page 142 on 348).

    Best regards,



    ------------------------------
    Patrick Neveu
    Collaboration Betters The World (CBTW)
    IBM Champion
    ------------------------------

    Original Message