Oracle WITH CLAUSE not working?

Question

Hi, I'm trying to use a WITH clause in a query but keep getting the message

ORA-00942: table or view does not exist

I've tried to create a simple query just as an example here:

WITH
test AS
(
SELECT COUNT(Customer_ID) FROM Customer
)
SELECT * FROM test;

But even this dosen't work, it just gives the message:

SELECT * FROM test; 2 3 4 5 6 SQL>
SELECT * FROM test
* ERROR at line 1:
ORA-00942: table or view does not exist

I've never used the WITH clause before, is there something simple I'm missing here? I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod Any advise would be appreciated. Thanks.

Answer 1

Take a look at this example

EDIT

a very basic sample:

create table emp (emp_id number, dept_id number);
insert into emp values (1,20);
insert into emp values (2,20);
insert into emp values (3,20);
insert into emp values (4,30);

with
emp_counter  as (select count(distinct emp_id) from emp),
dept_counter as (select count(distinct dept_id) from emp)
select * from emp_counter, dept_counter;

COUNT(DISTINCTEMP_ID) COUNT(DISTINCTDEPT_ID)
--------------------- ----------------------
                    4                      2

Answer 2

Your example works - just tried it (SQL*Plus log follows):

SQL> create table customer
  2  (customer_id number);
Table created.
SQL> with 
  2  test as 
  3  (select count(customer_id)
  4  from customer
  5  )
  6  select * from test;
COUNT(CUSTOMER_ID)
------------------
      0

Are you sure that you have privileges on the customer table or don't need a schema qualifier for it (if it is in a different schema)?

Answer 3

I believe you have a blank line in your script between the WITH clause and the SELECT:

SQL> WITH
  2  test AS
  3  (
  4  SELECT COUNT(Customer_ID) FROM Customer
  5  )
  6  
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

That is consistent with the fact that you got the error reported as being on "line 1" and SQL "select * from test", when this SQL should be on "line 6".

Answer 4

The error which you are getting means literally that the table for view does not exist in your current schema and has no synonym visible to your schema. For example, if I login as greg, and the table is in bob, then I should reference the table as bob.test.

SELECT * FROM bob.test

As for the WITH syntax, I am not familiar, but other answers are covering that fine.