Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  SQL client: Generate WHERE clause from resultset

    Posted Wed October 27, 2021 12:38 AM
    Edited by System Admin Wed March 22, 2023 11:51 AM

    Oracle 19c:


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


    #Maximo
    #AssetandFacilitiesManagement


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

    Posted Wed October 27, 2021 12:40 AM
    Edited by System Admin Wed March 22, 2023 11:49 AM
    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
    #Maximo
    #AssetandFacilitiesManagement


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

    Posted Tue November 23, 2021 10:48 AM
    Edited by System Admin Wed March 22, 2023 11:47 AM
    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

    Original Message


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

    Posted Thu November 25, 2021 09:04 AM
    Edited by System Admin Wed March 22, 2023 11:45 AM

    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


    #AssetandFacilitiesManagement
    #Maximo