Clash Royale CLAN TAG#URR8PPP

How to Have Function Definition and Subquery in With Clause of Oracle Select Statement?

I know the right syntax for having a function definition in the WITH clause. I know the right syntax for having a subquery in the WITH clause. But I have been unable to find an example of having a subquery and a function definition in the WITH clause of a SELECT statement.

WITH

WITH

WITH

SELECT

If I have:

with totals as ( select colum_name from some_table )
select sum(column_name) from totals;


How do I add a function definition in the WITH clause?

WITH

1 Answer
1

I don't think there's any such restriction. However, I suspect that your problem has to do with column aliasing. Here's what worked for me:

with totals as (select sum(column_name) c1 from some_table)
select c1 from totals;


Oracle might have complained because you were trying to do something like:

with totals as (select sum(column_name) from some_table)
select sum(column_name) from totals;


Unfortunately, this is a consequence of name resolution. The subquery's column will get named "sum(column_name)". Since sum is a function, there's no way to reference that column name without Oracle thinking you're referencing the function. You have to give it another name in order to reference it anywhere else.

Edit: It seems that you want to define a function as if you would a view subquery. I don't think anything like this is possible. View subqueries really only perform textual substitution.

PL/SQL functions require a whole different parser, name resolution, compilation process, etc. Having them work in queries alone is hard enough.

Sorry to say, but you'd have to define your packages/procedures/functions normally.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.