Select into variables from cte using if oracle-CodePudding

I need to save values from cte into variables in the procedure. My example:

WITH test_cte AS (
SELECT *
from(
    SELECT date_a,lastName,firstName,birthDate, rank() over(ORDER BY date_a desc) rnk
    FROM test_table a
    join test_table b ON b.id = a.bid 
)a1
WHERE rnk =1)

SELECT count(*) into count_a 
FROM test_table a
join test_table b ON b.id = a.bid
WHERE a.code = code_name;



IF count_a > 0 THEN
    SELECT date_a,lastName,firstName,birthDate
    into date_a_var,lastName_var,firstName_var,birthDate_var
    FROM test_cte;
ELSE 
date_a_var := NULL;
lastName_var := NULL;
firstName_var := NULL;
birthDate_var := NULL;
END IF;

but when I try to compile it I'm getting next error:

PL / SQL: ORA-00942: table or view does not exist

on FROM test_cte;

What can I do to solve this problem?

CodePudding user response：

In your code:

-- Start of first SELECT statement.
WITH test_cte AS (
  SELECT *
  from(
    SELECT date_a,lastName,firstName,birthDate, rank() over(ORDER BY date_a desc) rnk
    FROM test_table a
    join test_table b ON b.id = a.bid 
  )a1
  WHERE rnk =1
)
SELECT count(*) into count_a 
FROM test_table a
join test_table b ON b.id = a.bid
WHERE a.code = code_name;
-- End of first SELECT statement.

IF count_a > 0 THEN
  -- Start of second SELECT statement.
  SELECT date_a,lastName,firstName,birthDate
  into date_a_var,lastName_var,firstName_var,birthDate_var
  FROM test_cte;
  -- End of second SELECT statement.
ELSE 
  date_a_var := NULL;
  lastName_var := NULL;
  firstName_var := NULL;
  birthDate_var := NULL;
END IF;

It won't work because the test_cte only exists for the one statement and when you finish the statement's final SELECT statement then it no longer exists for the subsequent statements. (However, you do not use the sub-query factoring clause [a.k.a. CTE] in the SELECT for that statement so it is not clear why you need the WITH clause.)

Instead of trying to use COUNT, just get the data and handle the NO_DATA_FOUND exception if it occurs (also, from Oracle 12, you don't need to use RANK and can use FETCH FIRST ROW WITH TIES instead):

DECLARE
  date_a_var    test_table.date_a%TYPE;
  lastName_var  test_table.lastname%TYPE;
  firstName_var test_table.firstname%TYPE;
  birthDate_var test_table.birthdate%TYPE;
BEGIN
  BEGIN
    SELECT date_a,
           lastName,
           firstName,
           birthDate
    INTO   date_a_var,
           lastName_var,
           firstName_var,
           birthDate_var
    FROM   test_table a
           join test_table b ON b.id = a.bid 
    ORDER BY date_a DESC
    FETCH FIRST ROW WITH TIES;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      date_a_var    := NULL;
      lastName_var  := NULL;
      firstName_var := NULL;
      birthDate_var := NULL;
  END;

  -- Continue processing
END;

(Note: You may also get a TOO_MANY_ROWS exception if there are duplicate dates. Either use FETCH FIRST ROW ONLY or, before Oracle 12, the ROW_NUMBER analytic function.)