oracle sql - define date as variable in a with statement

Question

in oracle - i am trying to figure out how to define variables in a with statement.

when i define a variable as a number it works fine:

with a as(
    select 100 as query_rows
    from dual
)

,b as (
    select * from table1 where rownum=query_rows
)

select * from b --working great

however,if i want to define a date as a variable,i keep getting an error:

with a as(
    select DATE '2020-10-01' as query_date
    from dual
)

,b as (
    select * from table1 where table1.date=query_date
)

select * from b --  ORA-00904 : "query_date" is not a valid identifier

from oracle :ORA-00904

You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

so,why does the first query work and the second one doesn't?

Are you sure the first query "works great"? I don't believe that. It will fail for the exact same reason as the second query - this has nothing to do with the data type. In your first query, you are using the column name without referencing the subquery a from the with clause - just the same as in your second example. Oracle won't know what that identifier means - it's not about data types. So: did that first query really work for you, exactly as written? Really? — user5683823
– user5683823, Commented Nov 29, 2020 at 15:15

Popeye · Accepted Answer · 2020-11-29 14:51:13Z

1

You need to use the CTE table in query as follows:

with a as(
    select DATE '2020-10-01' as query_date
    from dual
)
,b as (
    select * from table1 cross join a where table1.date=a.query_date
)
Select * from b;

answered Nov 29, 2020 at 14:51

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2020-11-29 14:52:23Z

1

It is possible, but you need to refer to cte you are using(here using subquery):

with a as(
    select DATE '2020-10-01' as query_date
    from dual
) ,b as (
    select * 
    from table1 
    where table1.date = (SELECT query_date FROM a) -- IN if more than one row is allowed
) 
select * from b

answered Nov 29, 2020 at 14:52

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Ori Marko · Accepted Answer · 2020-11-29 15:06:18Z

0

Adding to @Tejash, you don't need to use b:

with a as(
    select DATE '2020-10-01' as query_date
    from dual
)
select * from table1 cross join a where table1.date=a.query_date

answered Nov 29, 2020 at 15:06

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

1 Comment

Popeye Over a year ago

Obviously, it is not needed. But I followed OP's query. But yeah, thanks for adding it here.

Collectives™ on Stack Overflow

oracle sql - define date as variable in a with statement

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related