Maximo

  • 1.  SQL client: Generate WHERE clause from resultset

    Posted Wed October 27, 2021 12:38 AM
    Edited by User1971 9 days ago

    Oracle 19c:


    In an SQL client:
    How to generate a WHERE clause from a resultset?



  • 2.  RE: SQL client: Generate WHERE clause from resultset

    Posted Wed October 27, 2021 12:40 AM
    Edited by User1971 7 days ago
    Oracle - WHERE clause:

    with a as (
    
    <<paste_your_query_here>>
    
    )
    select 'wonum in ('''
      || listagg(wonum, ''', ''') within group(order by wonum)
      || ''')' as where_clause
    from a
    
    
    
    WHERE_CLAUSE                                                                    
    --------------------------------------------------------------------------------
    wonum in ('WO11070', 'WO12018', 'WO1226', 'WO2765', 'WO3289', 'WO4403', 'WO4499'
    , 'WO5804', 'WO6107', 'WO6531', 'WO8160', 'WO8272', 'WO9910')                   
                                                                                    
    1 row selected.
    ​​


    Source: Generate WHERE clause from resultset ids?

    RFE: Button to generate list of IDs from List View resultset


  • 3.  RE: SQL client: Generate WHERE clause from resultset

    Posted 9 days ago
    Edited by User1971 9 days ago
    Filter syntax:

    For example,  filter a dialog or lookup with a list of values:
    =CONTRACT, =CREWID, =CREWWORKGROUP

    with a as (
    
    <<paste_your_query_here>>
        
        )
    select '='
      || listagg(attributename, ', =') within group(order by attributename)
      || '' as where_clause
    from a

    =AMCREW, =ASSETNUM, =ASSIGNEDOWNERGROUP, =CALCCALENDAR, =CALCORGID, =CALCSHIFT, =CALENDAR, =CGDIVISION, =CGFIRCODE, =CGHIERARCHYPATH, =CGVFAREQ, =CHANGEBY, =CINUM, =CLASSSTRUCTUREID, =COMMODITY, =COMMODITYGROUP, =CONTRACT, =CREWID, =CREWWORKGROUP



  • 4.  RE: SQL client: Generate WHERE clause from resultset

    Posted 7 days ago
    Edited by User1971 7 days ago

    SQL Server - WHERE clause:

    with a as (
    
    select * from test_table
    
    )
    select 'where id in (''' +
          string_agg(id, ''',''') within group(order by id) 
          + ''')' as where_clause
    from a​


    Result:

    where id in ('1','2','3')


    DB<>Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=dc98e5ab26745c15a8514078f42b7adc





    SQL Server - filter list:

    with a as (
    
    select * from test_table
    
    )
    select '=' +
          string_agg(id, ', =') within group(order by id) 
          as where_clause
    from a


    Result:

    =1, =2, =3


    DB<>Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=078efa6d52d9a3f044329ea528511535