CODEWITHSUNDEEP
sqloracle-database
Tiago Dias
Tiago Dias

Reputation: 71

Declaring and using variables using WITH clause

I have a query that uses WITH clause (subquery factoring) and i need to define a variable and assign a value to it to use in a couple of the subqueries within the WITH clause.

Is it possible?

Thanks.

I've tried some ways to define the variable and affect it but i always have syntax error.

Upvotes: 1

Views: 5773

Answers (2)

Popeye
Popeye

Reputation: 35920

You can use the following two methods:

1. Use within WITH clause

WITH MY_VARIABLE ( VAL ) AS (
    SELECT
        12 -- your variable value goes here
    FROM
        DUAL
), MY_MAIN_WITH_QUERY ( DIFFERENT_COLUMNS ) AS (
    SELECT
        1
    FROM
        MY_VARIABLE M
    WHERE
        M.VAL > 10 -- the use of a variable in your main query
)
SELECT
    *
FROM
    MY_MAIN_WITH_QUERY;

2. Use DEFINE

DEFINE VAL=12; -- declaring and assigning value to your variable
WITH MY_MAIN_WITH_QUERY ( DIFFERENT_COLUMNS ) AS (
    SELECT
        1
    FROM
        DUAL
    WHERE
        &VAL > 10 -- the use of your variable in your query
)
SELECT
    *
FROM
    MY_MAIN_WITH_QUERY;

Cheers!!

Upvotes: 2

user14063792468
user14063792468

Reputation: 956

You can always define your column value in the with clause.

with cte as ( select 1 as your_value from dual )

Then use it to your need. For example:

select your_value from cte

Upvotes: 0

Related Questions