Hello,
I have 2 tables – HPQC_TESTCASE(having ID as PK) and HPQC_TESTSTEPS.(Having TESTCASE_ID FK, which is relating to ID column of 1st table).This is basically a test report storing tables. Here test cases are stored in HPQC_TESTCASE table which is master and its steps are stored in the HPQC_TESTSTEPS table which is child table. A particular test case will/can have multiple test steps.
Relation between these 2 tables is mentioned above as 1st table's ID is present in 2nd table's TESTCASE_ID column which is FK. So basically this is a master detail type table relation. And the use of this query is when a user wants to make a copy of records from existing data based on conditions.
The intention of this query to provide feature of if user wants to have a copy of the master-detail records according to 2 where conditions. So, I am trying to implement logic is, firstly copying the records of master table then copying records of details table.
But i am facing issue of building a relation among the newly inserted records, as when new records are inserted into details table then the testcase_id column should have values of the newly inserted ID column. ID column values are trigger generated for 1st table.
Current Query:
declare
begin
-- Copying firstly at Testcase level
INSERT INTO hpqc_testcase (
SUBJECT,
TEST_NAME,
DESCRIPTION,
TYPE,
TEST_TYPE,
TRACK,
FUNCTION,
PROJECT_ID
)
SELECT
SUBJECT,
TEST_NAME,
DESCRIPTION,
TYPE,
TEST_TYPE,
:P9_TRACK_NAME_1,
FUNCTION,
:P9_PROJECT_NAME_1
FROM
hpqc_testcase
WHERE
project_id = :P9_PROJECT_NAME
AND track = :P9_TRACK_NAME;
-- copying at test step level now,
insert into hpqc\_teststeps (
STEP\_NO,
STEP\_NAME,
DESCRIPTION\_DSTEPS,
EXPECTED\_RESULT,
XO\_VALUE,
TESTER,
INPUT\_VALUE,
NAVIGATION\_PATH,
MODULE,
COMMENTS,
VALIDATION\_METHOD,
FUNCTIONAL\_OWNER,
PARTNER,
TESTER\_NAME,
TESTCASE\_ID, -- this table's TESTCASE\_ID = 1st table's ID
PROJECT\_ID,
TRACK\_NAME
)
select
STEP_NO,
STEP_NAME,
DESCRIPTION_DSTEPS,
EXPECTED_RESULT,
XO_VALUE,
TESTER,
INPUT_VALUE,
NAVIGATION_PATH,
MODULE,
COMMENTS,
VALIDATION_METHOD,
FUNCTIONAL_OWNER,
PARTNER,
TESTER_NAME,
(TESTCASE_ID), -- here, it should insert the newly generated IDs for the new records maintaining the relation
:P9_PROJECT_NAME_1,
:P9_TRACK_NAME_1
from hpqc_teststeps
WHERE
project_id = :P9_PROJECT_NAME
AND track_name = :P9_TRACK_NAME;
commit;
end;
Please provide your suggestions or any other feasible/efficient approach if applicable. Feel free to discuss for any other information required.