Oracle SQL query to find values in preconditions.

[Alexander Trenker]

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 

[Alexander Trenker]

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;