当前位置：文江博客话题详情

Oracle WITH CLAUSE 不起作用？

发布于 2024-07-19 02:55:00 字数 547 浏览 7 评论 0原文

我尝试在查询中使用WITH子句，但不断收到消息

ORA-00942: 表或视图不存在

我尝试创建一个简单的查询作为示例：

与
测试AS
（
从客户中选择 COUNT(Customer_ID)
)
从测试中选择*；

但即使这样也不起作用，它只是给出了这样的信息：

从测试中选择*； 2 3 4 5 6 SQL>
从测试中选择*
* 第 1 行出现错误：
ORA-00942: 表或视图不存在

我以前从未使用过WITH子句，我在这里缺少一些简单的东西吗？我正在使用 Oracle Database 10g 企业版版本 10.2.0.1.0 - Prod 任何建议将不胜感激。谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伪装你 2024-07-26 02:55:01

您的示例有效 - 刚刚尝试过（SQL*Plus 日志如下）：

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

您确定您对客户表有权限还是不需要它的架构限定符（如果它位于不同的架构中）？

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)?

回复收藏 0 原文

挽心 2024-07-26 02:55:01

您是否尝试过添加到脚本顶部

SET SQLBLANKLINES ON;

Have you tried adding to the top of the script

SET SQLBLANKLINES ON;

回复收藏 0 原文

作妖 2024-07-26 02:55:01

看一下这个示例

编辑

一个非常基本的示例：

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

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

回复收藏 0 原文

微凉 2024-07-26 02:55:01

您收到的错误实际上意味着视图的表在您当前的架构中不存在，并且没有对您的架构可见的同义词。例如，如果我以 greg 身份登录，并且该表位于 bob 中，那么我应该将该表引用为 bob.test。

SELECT * FROM bob.test

至于WITH语法，我不熟悉，但其他答案都涵盖了这一点。

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.

回复收藏 0 原文

Smile简单爱 2024-07-26 02:55:00

我相信你的脚本中的WITH子句和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

这与你在“第1行”和SQL“select * from test”上报告的错误这一事实是一致的，当这个SQL应该乘坐“6 号线”。

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".

回复收藏 0 原文

~没有更多了~