Refactoring a query-CodePudding

I have a simplified code like below:

PROCEDURE MY_PROC (d1 IN DATE) IS
CURSOR curs IS SELECT * FROM TAB1 WHERE date1 = d1;
result BOOLEAN;
rTab1 TABLE1%ROWTYPE;
BEGIN
OPEN curs;
    FETCH curs INTO rTab1 ;
    result := curs%FOUND;
CLOSE curs;
IF result = FALSE THEN
    rTab1.NUM_ID := 0;
END IF;
SELECT * FROM TAB2 WHERE NVL(NUM_ID, 0) = rTab1.NUM_ID;
END;

Is it possible to write everything in one query? I mean without having to chceck if rTab1.NUM_ID exists. Maybe with join? And get same results at the end like above? I mean something like:

SELECT * FROM TAB1, TAB2 
WHERE NVL(TAB1.NUM_ID, 0) = TAB2.NUM_ID
AND date1 = d1;

CodePudding user response：

The code is looking for tab1 rows where the date matches the given date d1.

If there is one matching row, then its num_id is used.
If there is more than one matching row, then one of their num_ids is used arbitrarily.
If there is no matching row, the value 0 is used.

Then with that value the code selects all matching tab2 rows.

This can be done with a single query. To get only one tab1.num_id, use MIN, MAX or ANY_VALUE. To get a zero when there is no date match use NVL or COALESCE.

SELECT *
FROM tab2
WHERE NVL(num_id, 0) = (SELECT NVL(MIN(num_id), 0) FROM tab1 WHERE date1 = d1);