CODEWITHSUNDEEP
sqloracle-database
jeff
jeff

Reputation: 3742

sql statement that loops over selected values and performs other sql calls using these loop values

I'm trying to update a table of Questions (ATTRIBUTE_ID) associated to CHECKLIST_INSTANCE_ID's where the checklist has not been completed in a homegrown work flow tool. We have revised a paper checklist with new questions, and modified some old questions and need to resynch the questions to incomplete checklists.

For each of the following checklist instance ids, calc ids, and calc revs

(Statement A)    
SELECT CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV FROM CHECKLIST_INSTANCE WHERE CHECKLIST_CLASS_ID = '200' and (CHECKLIST_STATE is null OR CHECKLIST_STATE = '0')

drop the Questions (ATTRIBUTE_ID) in the answer table (only need checklist instance Id as the pk)

(Statement B)
DELETE FROM CHECKLIST_ANSWER WHERE CHECKLIST_INSTANCE_ID='The instance Id from statement A'

and reinsert the latest rev questions (ATTIBUTE_ID) using the same instance id, calc id, calc rev above. But using the latest questions from this query:

(Statement C)
SELECT CHECKLIST_ATTRIBUTE.ATTRIBUTE_ID FROM CHECKLIST_ATTRIBUTE WHERE IS_ACTIVE='1' AND  CHECKLIST_ATTRIBUTE.CHECKLIST_CLASS_ID = '200' 

(Statement D)
INSERT INTO CHECKLIST_ANSWER ( CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV, ATTRIBUTE_ID) Values ( 'CHECKLIST_INSTANCE_ID from statement A', 'CALC_ID from statement A', ' CALC_REV from statement A', 'attribute id from select statement C')

There are over 60 questions I need to swap out on about 40 checklist instances so I'm hoping to not have to hand craft a sqlloader input file but rather use the 4 statements above somehow.

Edit OK I have a query that does the cross-multiply against the new question Id's

with 
   incomplete_checklists AS (SELECT CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV 
   FROM TPQOT_CHECKLIST_INSTANCE WHERE CHECKLIST_CLASS_ID = '1257877690209' and (CHECKLIST_STATE is null OR CHECKLIST_STATE = '0')
),
   newest_questions as (SELECT TPQOT_CHECKLIST_ATTRIBUTE.ATTRIBUTE_ID FROM 
   NAVARCH.TPQOT_CHECKLIST_ATTRIBUTE  WHERE IS_ACTIVE='1' AND
   TPQOT_CHECKLIST_ATTRIBUTE.CHECKLIST_CLASS_ID = '1257877690209' )
SELECT CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV, ATTRIBUTE_ID <-will change to INSERT when ready
FROM incomplete_checklists , newest_questions

the tough part is I now need to do the Delete statement B

Upvotes: 0

Views: 65

Answers (2)

Laurence
Laurence

Reputation: 10976

You should be able to combine these into two queries like so:

delete
    checklist_answer a
where
    exists (
        select
            'x'
        from
            checklist_instance i
        where
            i.checklist_instance_id = a.checklist_instance_id and
            i.checklist_class_id = '200' and
            nvl(checklist_state, '0') = '0'
    )

insert into checklist_answer (
    checklist_instance_id,
    calc_id,
    calc_rev,
    attribute_id
) select
    i.checklist_instance_id,
    i.calc_id,
    i.calc_rev,
    c.attribute_id
from
    checklist_instance i
        cross join
    checklist_attribute c
where
    i.checklist_instance_id = c.checklist_instance_id and
    i.checklist_class_id = '200' and
    nvl(checklist_state, '0') = '0' and
    c.is_active = '1' and
    c.checklist_class_id = '200'

it might also be possible to combine them into a single query using Merge

Upvotes: 1

jeff
jeff

Reputation: 3742

So here are the SQL calls I eventually used

DELETE FROM CHECKLIST_ANSWER WHERE CHECKLIST_INSTANCE_ID IN (
    SELECT CHECKLIST_INSTANCE_ID FROM CHECKLIST_INSTANCE 
        WHERE CHECKLIST_CLASS_ID = '200' AND 
        (CHECKLIST_STATE is null OR CHECKLIST_STATE = '0') )

... and then...

INSERT INTO CHECKLIST_ANSWER ( 
    CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV, ATTRIBUTE_ID)
WITH incomplete_checklists AS (
    SELECT CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV
    FROM CHECKLIST_INSTANCE 
    WHERE CHECKLIST_CLASS_ID = '200' AND 
    (CHECKLIST_STATE is null OR CHECKLIST_STATE = '0')
 ),
     newest_questions AS (
    SELECT CHECKLIST_ATTRIBUTE.ATTRIBUTE_ID
    FROM CHECKLIST_ATTRIBUTE  
    WHERE IS_ACTIVE='1' AND CHECKLIST_ATTRIBUTE.CHECKLIST_CLASS_ID = '200' 
  )
  SELECT CHECKLIST_INSTANCE_ID, CALC_ID, CALC_REV, ATTRIBUTE_ID
  FROM incomplete_checklists, newest_questions

Upvotes: 0

Related Questions