WITH temp1 (group_id, path) AS ( select group_id, cast(name as varchar(1000)) from blgroup bg1 where parent_group_id = 0 UNION ALL select bg1.group_id, cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000)) from blgroup bg1 inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id ) select temp1.path || '/' || j.name,bo.name,jov.option_value from temp1 inner join job j on temp1.group_id = j.group_id join job_option_value jov on j.job_id = jov.job_id and j.job_version_id = jov.job_version_id join bloption bo on jov.option_id = bo.option_id where jov.option_id = 301 and j.is_latest_version = 1 and j.is_saved_explicitly = 1 and j.is_deleted = 0 ;
should be this. unlimited is either 0 or 2,147,483,647 so you can add a clause to only show results where jov.option_value > somenumber. you can also filter on the job types w/ j.object_type_id if this is only for certain jobs. remember that some job types show as unlimited i think but they are not - for example a type 2 (run once pass targets as arg) i think shows as unlimited in the option_value but it's actually 1 because of how it works.
Thank you, Bill.
Can we also add one more condition- "any job set to unlimited parallelism and with more than 30 targets in it"?
30 targets directly in the job probably. 30 targets via a smart group, no.?