Subversion Repositories DevTools

SELECT DISTINCT pv.pv_id, pv.pkg_id, pv.v_ext, pkg.pkg_name, pv.pkg_version, qry.prod_id as prev_pv_id
FROM
  bom_contents bc,
  network_nodes nn,
  operating_systems os,
  os_contents osc,
  packages pkg,
  package_versions pv,
  (
  SELECT osc.prod_id, pv.pkg_id
    FROM bom_contents bc,
         network_nodes nn,
         operating_systems os,
         os_contents osc,
         package_versions pv
    WHERE osc.os_id = os.os_id
      AND os.node_id = nn.node_id
      AND nn.node_id = bc.node_id
      AND bc.bom_id = :SBOM_B
      AND osc.prod_id = pv.pv_id
      AND pv.is_patch IS NULL
  ) qry
WHERE osc.os_id = os.os_id
  AND os.node_id = nn.node_id
  AND nn.node_id = bc.node_id
  AND bc.bom_id = :SBOM_A
  AND osc.prod_id = pv.pv_id
  AND pv.pkg_id = pkg.pkg_id
  AND osc.prod_id NOT IN
      (
      SELECT osc.prod_id
        FROM bom_contents bc,
             network_nodes nn,
             operating_systems os,
             os_contents osc
        WHERE osc.os_id = os.os_id
          AND os.node_id = nn.node_id
          AND nn.node_id = bc.node_id
          AND bc.bom_id = :SBOM_B
      )
  AND qry.pkg_id = pv.pkg_id
ORDER BY UPPER(pkg.pkg_name || ' ' || pv.pkg_version)