CODEWITHSUNDEEP
sqloracle-database
hacikho
hacikho

Reputation: 147

How to Combine two queries into one in Oracle SQL

We have an error in production, luckily have a manual solution for this, however I have to run below two queries every morning to fix the error. This is so manual, I want to automate this and combine two queries into one. However we only have this error in production and not in DEV or QA, if I mess up with the combining query that will end up a chaos, So I need your expertise.

1st Query brings project numbers

select id,  ugenProjectNumber
from unifier_uxpecai
where (pecaiChecklistNumber = 0 or pecaiChecklistNumber is null)
or (pecaiChecklistItemNumber = 0 or pecaiChecklistItemNumber is null)

2nd Query fix broken links between action items and list items, I manually put 1st query results unique project numbers into second query and run the second query per each unique project numbers.

update unifier_uxpecai pai
set (pai.pecaiChecklistNumber, pai.pecaiChecklistItemNumber) =
(
   select pcl.id, pcli.id
   from unifier_uxpecl pcl
   inner join unifier_uxpecl_lineitem pcli on pcli.uuu_tab_id = 0 and 
   pcli.record_id = pcl.id
   where pcl.ugenProjectNumber = 'GL-16-161010-143502'
   and pcli.pecItemActionItemBPC = pai.id
)
where exists
(
   select pcli.pecItemActionItemBPC
   from unifier_uxpecl pcl
   inner join unifier_uxpecl_lineitem pcli on pcli.uuu_tab_id = 0 and 
   pcli.record_id = pcl.id
   where pcl.ugenProjectNumber = 'GL-16-161010-143502'
   and pcli.pecItemActionItemBPC = pai.id
)
and (pai.pecaiChecklistNumber = 0 or pai.pecaiChecklistItemNumber = 0)

Upvotes: 1

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You can incorporate the logic into the queries:

update unifier_uxpecai pai
set (pai.pecaiChecklistNumber, pai.pecaiChecklistItemNumber) =
(select pcl.id, pcli.id
 from unifier_uxpecl pcl join
      unifier_uxpecl_lineitem pcli
      on pcli.uuu_tab_id = 0 and pcli.record_id = pcl.id
 where pcl.ugenProjectNumber in (select ugenProjectNumber
                                 from unifier_uxpecai
                                 where (pecaiChecklistNumber = 0 or pecaiChecklistNumber is null) or
                                       (pecaiChecklistItemNumber = 0 or pecaiChecklistItemNumber is null
                                ) and
       pcli.pecItemActionItemBPC = pai.id
)
where exists
(
   select pcli.pecItemActionItemBPC
   from unifier_uxpecl pcl join
        unifier_uxpecl_lineitem pcli
        on pcli.uuu_tab_id = 0 and 
           pcli.record_id = pcl.id
   where pcl.ugenProjectNumber in (select ugenProjectNumber
                                   from unifier_uxpecai
                                   where (pecaiChecklistNumber = 0 or pecaiChecklistNumber is null) or
                                         (pecaiChecklistItemNumber = 0 or pecaiChecklistItemNumber is null
                                  ) and
        pcli.pecItemActionItemBPC = pai.id
) and
(pai.pecaiChecklistNumber = 0 or pai.pecaiChecklistItemNumber = 0)

Upvotes: 1

Related Questions