oracle-databasevariablesplsqlselect-intowith-clause

Oracle PL/SQL Select into variable using WITH clause


I have a WITH clause that gives me the desired result, and i'm trying to put it in a variable. I have omitted code for simplicity. The last line of code is most relevant.

WITH ALL_VE_ERRORS AS (

        SELECT *
        FROM ASN.AN_VALIDATION_ERRORS
        WHERE ...

    ), FILTER_STATUS AS (

        SELECT *
        FROM ALL_VE_ERRORS
        WHERE ...

      ) SELECT UNIT_DISCREPANCY FROM FILTER_STATUS INTO W_UNIT_DISCREPANCY; <-- like this

But this doesn't work, the compiler doesn't like this. I also tried putting it first like this:

SELECT INTO W_UNIT_DISCREPANCY <-- or like this
       WITH ALL_VE_ERRORS AS (...

Anyone know the proper syntax to do something like this?


Solution

  • If you have no reasons I can't see, you don't need two tables in your WITH clause; you could simplify it this way:

    WITH ALL_VE_ERRORS AS (    
        SELECT *
        FROM ASN.AN_VALIDATION_ERRORS
        WHERE ...    
    )
    SELECT UNIT_DISCREPANCY
    INTO W_UNIT_DISCREPANCY
    FROM ALL_VE_ERRORS
    WHERE ...
    

    Otherwise, you can use:

    WITH ALL_VE_ERRORS AS (    
            SELECT *
            FROM ASN.AN_VALIDATION_ERRORS
            WHERE ...    
        ), FILTER_STATUS AS (    
            SELECT *
            FROM ALL_VE_ERRORS
            WHERE ...    
          )
    SELECT UNIT_DISCREPANCY
    INTO W_UNIT_DISCREPANCY
    FROM FILTER_STATUS