CODEWITHSUNDEEP
oracle-databasevariablesplsqlselect-intowith-clause
SomeRandomDeveloper
SomeRandomDeveloper

Reputation: 489

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?

Upvotes: 0

Views: 4437

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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

Upvotes: 2

Related Questions