I have apex application and I got duplicates when Inserting ,
How can I avoid duplicate rows
This is the code :
DECLARE
CURSOR p1 IS
SELECT DISTINCT
sd.TEST_NO,
b.TEST_NAME_ENG,
a.PATIENT_NO,
a.ORDER_ID,
sd.SAMPLE_ID,
a.SECTION_ID,
b.SAMPLE_TYPE,
b.TEST_CONTAINER,
b.TEST_VOLUME,
a.SAMPLE_STATUS,
a.CUST_NO,
DECODE(o.ORDER_PRIORITY, 1, 'URGENT', 2, 'ROUTINE') AS ORDER_PRIORITY ,
p.TEST_NAME,p.REFERENCE_RANGE, p.TEST_UNIT , p.SERIAL
FROM
LAB_SAMPLE_HEADER a,
LAB_TESTS b,
LAB_SAMPLE_DETAILS sd,
LAB_ORDERS o ,
LAB_TEMPLATE_DETAILS p
WHERE
sd.TEST_NO = b.TEST_NO
AND a.ORDER_ID = o.ORDER_ID
AND sd.ORDER_ID = o.ORDER_ID
AND a.ORDER_ID = sd.ORDER_ID
AND a.SAMPLE_ID = sd.SAMPLE_ID
AND a.PATIENT_NO = :P60_MRN
AND sd.TEST_NO = p.TEST_NO
AND a.order_id = :P60_ORDER
AND B.TEST_NO IN (
SELECT REGEXP_SUBSTR(:P60_TEST_NO, '[^,]+', 1, LEVEL) AS TESTNO
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(:P60_TEST_NO, ',') + 1
);
CURSOR para_tests(p_test_no IN NUMBER) IS
select TEST_NO,TEST_NAME,REFERENCE_RANGE, TEST_UNIT , SERIAL
from LAB_TEMPLATE_DETAILS para
where para.test_no = p_test_no;
v_profile_exists number; -- Variable to check if the TEST_NO belongs to a profile
v_para_exists number; -- Variable to check if the TEST_NO belongs to a parasitology
BEGIN
FOR i IN p1 LOOP
-- Check if the current TEST_NO is part of a template
SELECT COUNT(*) INTO v_para_exists
FROM LAB_TEMPLATE_DETAILS
WHERE test_no = i.TEST_NO;
IF v_para_exists > 0 THEN
-- TEST_NO is part of a parasitology, insert only parasitology tests
FOR p IN para_tests(i.TEST_NO) LOOP
INSERT INTO LAB_PARA_RESULTS
(
ORDER_ID, SAMPLE_ID, PATIENT_NO, TEST_NO, TEST_NAME, TEST_RESULT , REFERENCE_RANGE ,
TEST_UNIT , SERIAL , EXAMINED_BY, EXAMINED_DATE, APPROVED_BY, APPROVED_DATE, CUST_NO , SAMPLE_STATUS )
VALUES (
i.ORDER_ID, -- ORDER_ID from cursor
i.SAMPLE_ID, -- SAMPLE_ID from cursor
i.PATIENT_NO, -- PATIENT_NO from cursor
p.TEST_NO, -- TEST_NO from para
i.TEST_NAME ,
NULL ,
i.REFERENCE_RANGE ,
i.TEST_UNIT ,
i.SERIAL , -- SERIAL
NULL , -- EXAMINED_BY
NULL, -- EXAMINED_DATE
NULL, -- APPROVED_BY
NULL , -- APPROVED_DATE
i.CUST_NO, -- CUST_NO from cursor
3 -- SAMPLE_STATUS
);
END LOOP;
end if;
END LOOP;
COMMIT; -- Commit the transaction
END;
the cursor p1 return correct number of rows without duplicates
the cursor para_tests also return correct number without duplicates
but when inserting rows its duplicated
for example cursor p1 returns 10 rows but when inserting its duplicated each row 10 time with total of 100 rows .
How to avoid duplicates in the loop when INSERT INTO LAB_PARA_RESULTS ?
You have not provided any sample data so it is impossible to test but, in general, you can (and should) avoid cursors and loops and do everything in a single query if possible:
BEGIN
INSERT INTO LAB_PARA_RESULTS (
ORDER_ID,
SAMPLE_ID,
PATIENT_NO,
TEST_NO,
TEST_NAME,
TEST_RESULT,
REFERENCE_RANGE,
TEST_UNIT,
SERIAL,
EXAMINED_BY,
EXAMINED_DATE,
APPROVED_BY,
APPROVED_DATE,
CUST_NO,
SAMPLE_STATUS
)
SELECT DISTINCT
a.ORDER_ID,
sd.SAMPLE_ID,
a.PATIENT_NO,
sd.TEST_NO,
p.TEST_NAME,
NULL, -- REFERENCE RANGE
p.REFERENCE_RANGE,
p.TEST_UNIT,
p.SERIAL,
NULL, -- EXAMINED_BY
NULL, -- EXAMINED_DATE
NULL, -- APPROVED_BY
NULL, -- APPROVED_DATE
a.CUST_NO,
3 -- SAMPLE_STATUS
FROM LAB_TESTS b
INNER JOIN LAB_SAMPLE_DETAILS sd
ON sd.TEST_NO = b.TEST_NO
INNER JOIN LAB_ORDERS o
ON sd.ORDER_ID = o.ORDER_ID
INNER JOIN LAB_SAMPLE_HEADER a
ON a.ORDER_ID = o.ORDER_ID
AND a.SAMPLE_ID = sd.SAMPLE_ID
INNER JOIN LAB_TEMPLATE_DETAILS p
ON sd.TEST_NO = p.TEST_NO
WHERE a.PATIENT_NO = :P60_MRN
AND a.order_id = :P60_ORDER
AND ',' || :P60_TEST_NO || ',' LIKE '%,' || B.TEST_NO || ',%';
COMMIT;
END;
Note:
COUNT check is implicitly performed by joining LAB_TEMPLATE_DETAILS p so you do not need to explicitly duplicate it.CURSOR para_tests by joining LAB_TEMPLATE_DETAILS p so doing it in a second (nested) cursor is redundant (and is probably the cause of your issue).