This is a bare bones strip of my actual code, but I would like to undestand how to do this so I can apply it throughout. I have code like this:
declare
use_t3 varchar2(1): = 'N'; //Y or N
use_t4 varchar2(1): = 'N'; //Y or N
begin
INSERT INTO MY_BIG_TABLE
SELECT
t3.qid AS "QID",
(SELECT t5.flag FROM t5 WHERE(t5.qid = t4.qid)) AS "FLAG"
FROM
t1 p,
t2 pm,
t3 pms,
t4 pmo
WHERE
t1.id = t2.qid
AND t3.day = 'Monday'
AND t2.id = t4.pmd_id(+)
AND t4.date IS NULL
end;
Now I have two variables:
use_t3
use_t4
If use_t3 and use_t4 are both equal to 'Y' (which means Yes, use these tables) then the query should be run as is shown exactly as both tables should be used (only either use_t1 or use_t2 can be equal to 'N', which means there will always be one which is qual to 'Y').
If use_t3 = 'N' and use_t4 = 'Y' then I would like the t3.qid column to still show but to return null, and for the condition:
t3.day = 'Monday'
to not be used.
If use_t3 = 'Y' and use_t4 = 'N' then I would like the "FLAG" column to return blank values, and the conditions:
t2.id = t4.pmd_id(+)
AND t4.date IS NULL
to not be used.
I hope this makes sense.
Cheers
Aucun commentaire:
Enregistrer un commentaire