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?
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