Can't use column names in select query on sqlf

问题:

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?

回答1:

[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 |

回答2:

In Oracle when you use mixed case names, you must always use double quotes around them. It assumes that identifiers will be all upper case.

So to access a column called ProductName you should run:

select "ProductName" from products

回答3:

I see your column names between double quotes " ".

This can use when you want to entitle your column names with special charecters.

So you can use :

select "ProductName" from products;