Oracle Sql : unable to use 'With' clause when using group by 'Cube'

Question

I have the following sample code as reference:

Create table wait_weekly as select XXX
.....;

Create table wait_weekly_prev as select 
.... 
from 
Wait_weekly
group by 

cube(var1, var2);

This works fine.

However, if I put the table wait_weekly either in the 'With' clause or directly in a subquery, like

1. with wait_weekly as (select XXX)
2. Create table wait_weekly_prev as select .... from (select XXX...) group by

cube(var1, var2); `

it will give me the same error message and won't recognize a variable is an invalid identifier.. Any suggestion? thanks.

Here is the sample code, but the reality is that it works in this sample code. Just imagine it's not working, and the error message is that 'invalid identifier for one of the variables in table Test". I did remove the cube( lastname, sex) and replaced with nvl(Lastname,'All_lastname') as Lastname, nvl(Sex,'All_sex') as Sex, and the codes work; but I do need cube( ..., ...).

Hope this is clear enough, thanks.

create table test (
Lastname VARCHAR2(12),
Sex  VARCHAR2(12),
Age  NUMBER);

insert all
into test (Lastname, Sex, Age) values ('Sun', 'M', 8)
into test (Lastname, Sex, Age) values ('Thomas','M',12)
into test (Lastname, Sex, Age) values ('Sun','F',13)
into test (Lastname, Sex, Age) values ('Thomas','F',15)
into test (Lastname, Sex, Age) values ('Berg','F',18)
SELECT 1 FROM DUAL;

Example queries:

with test_1 as
(select * from test where lastname <> 'Berg')
select 
  nvl(Lastname,'All_lastname') as Lastname, 
  nvl(Sex,'All_sex') as Sex, 
  sum(Age) as Age 
from test_1
group by  cube(Lastname, Sex);
                        
select 
  nvl(Lastname,'All_lastname') as Lastname, 
  nvl(Sex,'All_sex') as Sex, 
  sum(Age) as Age 
from (select * from test where lastname <> 'Berg')
group by cube(Lastname, Sex);

Answer 1

等待大神答复