Automic Workload Automation

Expand all | Collapse all

Oracle SQL query to find values in preconditions.

Jump to Best Answer
  • 1.  Oracle SQL query to find values in preconditions.

    Posted 12-18-2018 07:38 PM

    Hi Automic and Oracle database experts,

     

    I have a workflow with the following precondition:

     

     

    Now I would need a query that shows all workflows containing 'set object variable 1 to 1'.

     

    Here's what I have so far:

     

    SELECT
    jpov_oh_idnr,
    oh_name,
    jpov_jpp_lnr,
    COUNT(jpov_jppo_lnr)
    FROM
    uc4.jpov
    JOIN uc4.oh ON oh_idnr = jpov_oh_idnr
    inner join jppo on jppo_oh_idnr = oh_idnr
    WHERE
    TO_CHAR(jpov_value) = '1'
    and JPPO_CarName = 'SET VALUE'
    GROUP BY
    oh_name,
    jpov_oh_idnr,
    jpov_jpp_lnr
    HAVING
    COUNT(jpov_jppo_lnr) > 1;

    The approach here is to look for more than one value '1' in the JPOV_JPPO_LNR, but this also shows other conditions like `return code = 1'.

     

    Any help on getting this figure out is highly appreciated.

     

    Cheers,

    Alexander 



  • 2.  Re: Oracle SQL query to find values in preconditions.
    Best Answer

    Posted 04-17-2019 01:02 PM

    This SQL statement 

     

    SELECT
    oh_client, oh_name, jpp_lnr, jpp_object,
    jpov_vname, jppo_carname, jpov_value, jppo_active, oh_lastdate
    FROM
    jppo
    INNER JOIN oh ON oh_idnr = jppo_oh_idnr
    INNER JOIN jpp ON jpp_lnr = jppo_jpp_lnr and jpp_oh_idnr = oh_idnr
    LEFT OUTER JOIN jpov 
    ON ( jppo_oh_idnr = jpov_oh_idnr
    AND jppo_jpp_lnr = jpov_jpp_lnr
    AND jppo_lnr = jpov_jppo_lnr
    AND jppo_location = jpov_location )
    LEFT OUTER JOIN uc4.uc_car ON uc_car.car_name = jppo_carname
    WHERE
    oh_deleteflag = 0
    AND jppo_carname = 'SET VALUE'
    AND jpov_vname IN ( 'xc_p01', 'XC_P01' ) 
    AND ( jpov_value LIKE '1%'
    OR jpov_value LIKE '2%'
    OR jpov_value LIKE '3%'
    OR jpov_value LIKE '4%'
    OR jpov_value LIKE '5%'
    OR jpov_value LIKE '6%'
    OR jpov_value LIKE '7%'
    OR jpov_value LIKE '8%'
    OR jpov_value LIKE '9%' )
    ORDER BY
    oh_idnr,
    jppo_location,
    jppo_lnr,
    jpov_vname;