无法在 sqlfiddle (oracle) 上的选择查询中使用列名 [英] Can't use column names in select query on sqlfiddle (oracle)

问题描述

如果我不正确地使用 sqlfidle 或者这缺少功能，我不知道吗?

重现步骤:

1. 选择 oracle 选项(左上角)

2. 创建表并插入数据:

   CREATE TABLE 产品("P_Id" int, "ProductName" varchar2(10), "UnitPrice" numeric, "UnitsInStock" int, "UnitsOnOrder" int)//全部插入INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (1, 'Jarlsberg', 10.45, 16, 15)INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (2, '马斯卡彭', 32.56, 23, NULL)INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (3, 'Gorgonzola', 15.67, 9, 20)SELECT * FROM 双//

3. 构建架构

4. 运行查询并验证结果是否正确

   select * from products

5. 使用列名(任意)运行查询并得到错误:

   从产品中选择产品名称

给出错误:

<块引用>

ORA-00904:PRODUCTNAME":标识符无效

我的查询是错误的还是在 sqlfiddle 上不能在选择查询中使用列名?我是否有任何解决方法来继续测试我的查询?

解决方案

[TL;DR] The最简单的事情是永远不要在对象名称周围使用双引号，而只是让 oracle 以默认方式管理区分大小写.

但是，您可以在 SQLFiddle 中使用双引号:

SQL 小提琴

Oracle 11g R2 架构设置:

CREATE TABLE 产品("P_Id" int, "ProductName" varchar2(10), "UnitPrice" numeric, "UnitsInStock" int, "UnitsOnOrder" int)//全部插入INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (1, 'Jarlsberg', 10.45, 16, 15)INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (2, '马斯卡彭', 32.56, 23, NULL)INTO 产品(P_Id"、ProductName"、UnitPrice"、UnitsInStock"、UnitsOnOrder")值 (3, 'Gorgonzola', 15.67, 9, 20)SELECT * FROM 双//

查询 1:

SELECT "ProductName" FROM products

结果:

<代码>|产品名称 ||-------------||贾尔斯贝格 ||马斯卡彭 ||戈尔贡佐拉 |

<块引用>

5. 使用列名(任意)运行查询并得到错误:

   从产品中选择产品名称

给出错误:

<块引用>

ORA-00904:PRODUCTNAME":标识符无效

Oracle 数据库默认区分大小写；但是，默认情况下，它们也会将所有内容转换为大写，以便区分大小写从您(用户)那里抽象出来.只有当您使用双引号时，Oracle 才会使用您为标识符指定的大小写.

由于您在 CREATE TABLE 语句中使用了带引号的标识符，因此您还需要在 SELECT 语句中使用带引号的标识符，并在表创建中使用完全相同的大小写.>

因此，列名不是 ProductName，而是 "ProductName"(带双引号).

更好的解决方案是不使用双引号:

SQL 小提琴

Oracle 11g R2 架构设置:

创建表格产品(P_Id int,产品名称 varchar2(10),单价数字，UnitsInStock int,UnitsOnOrder int)//INSERT INTO 产品( P_Id, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder )SELECT 1, 'Jarlsberg', 10.45, 16, 15 from DUAL UNION ALLSELECT 2, 'Mascarpone', 32.56, 23, NULL FROM DUAL UNION ALL选择 3, 'Gorgonzola', 15.67, 9, 20 from DUAL//

查询 1:

SELECT ProductName FROM products

结果:

<代码>|产品名称 ||-------------||贾尔斯贝格 ||马斯卡彭 ||戈尔贡佐拉 |

I'm not if am I using sqlfidle incorrectly or if this is missing functionality?

Steps to reproduce:

1. Select oracle option (top left)

2. create table and insert data:

   CREATE TABLE products
   ("P_Id" int, "ProductName" varchar2(10), "UnitPrice" numeric, "UnitsInStock" int, "UnitsOnOrder" int)
   //
   
   INSERT ALL 
   INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
    VALUES (1, 'Jarlsberg', 10.45, 16, 15)
   INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
    VALUES (2, 'Mascarpone', 32.56, 23, NULL)
   INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
    VALUES (3, 'Gorgonzola', 15.67, 9, 20)
   SELECT * FROM dual
   //
   

3. Build schema

4. run query and verify result is correct

   select * from products
   

5. Run query with column name (any) and get the error:

   select ProductName from products
   

error given:

ORA-00904: "PRODUCTNAME": invalid identifier

Is my query wrong or isn't it possible on sqlfiddle to use the column names in the select query? Do I have any workarounds to keep testing my query?

解决方案

[TL;DR] The simplest thing to do is to never use double quotes around object names and just let oracle manage the case-sensitivity in its default manner.

However, you can use double-quotes in SQLFiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE products
("P_Id" int, "ProductName" varchar2(10), "UnitPrice" numeric, "UnitsInStock" int, "UnitsOnOrder" int)
//

INSERT ALL 
INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
 VALUES (1, 'Jarlsberg', 10.45, 16, 15)
INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
 VALUES (2, 'Mascarpone', 32.56, 23, NULL)
INTO products ("P_Id", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")
 VALUES (3, 'Gorgonzola', 15.67, 9, 20)
SELECT * FROM dual
//

Query 1:

SELECT "ProductName" FROM products

Results:

| ProductName |
|-------------|
|   Jarlsberg |
|  Mascarpone |
|  Gorgonzola |

5. Run query with column name (any) and get the error:

   select ProductName from products

error given:

ORA-00904: "PRODUCTNAME": invalid identifier

Oracle databases are, by default, case sensitive; however, they will also, by default, convert everything to upper-case so that the case sensitivity is abstracted from you, the user. It is only when you use double-quotes that Oracle will use the case you specify for the identifier.

Since you used quoted identifiers in the CREATE TABLE statement you will also need to use quoted identifiers in the SELECT statements with the exact case used in table creation.

So, the column name is not ProductName it is "ProductName" (with the double-quotes).

A better solution is to not use double quotes:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE products(
  P_Id         int,
  ProductName  varchar2(10),
  UnitPrice    numeric,
  UnitsInStock int,
  UnitsOnOrder int
)
//

INSERT INTO products ( P_Id, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder )
  SELECT 1, 'Jarlsberg', 10.45, 16, 15 FROM DUAL UNION ALL
  SELECT 2, 'Mascarpone', 32.56, 23, NULL FROM DUAL UNION ALL
  SELECT 3, 'Gorgonzola', 15.67, 9, 20 FROM DUAL
//

Query 1:

SELECT ProductName FROM products

Results:

| PRODUCTNAME |
|-------------|
|   Jarlsberg |
|  Mascarpone |
|  Gorgonzola |

这篇关于无法在 sqlfiddle (oracle) 上的选择查询中使用列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！