Oracle SQL: Select *, statement problem (vs SQL Server)-CodePudding

In SQL Server, I can write some code with select * statement, but it returns an error when writing in Oracle.

Here is an example - let's say I got a table Order which contains these columns:

[Date] | [Order_ID] | [Amt] | [Salesman]

In SQL Server, I can write code like this :

SELECT 
    *, 
    CASE WHEN [Amt] >= 0 THEN [Order_ID] END AS [Order_with_Amt] 
FROM Order

The result will be :

Date       | Order_ID | Amt | Salesman | Order_with_Amt
----------- ---------- ----- ---------- ---------------
01/01/2022 | A123     | 100 | Peter    | A123
01/01/2022 | A124     | 0   | Sam      | null

However, in Oracle, I cannot write the code as :

SELECT
    *, 
    CASE WHEN "Amt" >= 0 THEN "Order_ID" END AS "Order_with_Amt"   
FROM Order

It will throw an error :

ORA-00923: FROM keyword not found where expected

Any suggestion on this issue?

CodePudding user response：

I see five things.

The two databases are different dialects of SQL, and so of course there are some features that work differently between them, even if this feature

The top branch has a plain* but can't be combined with anything else - there is no loop around to other options. The branches that do allow you to loop and add comma-separated terms have .* prefixed by a table (or view) or a table alias.

You are also using quoted identifiers, both for your column names and column expression aliases. It might be worth reading up on Oracle's object name rules, and seeing if you really need and want to use those.

If you create a table with a column with a quoted mixed-case name like "Amt" then you have to refer to it with quotes and exactly the same casing everywhere, which is a bit of a pain and easy to get wrong.

If you create it with an unquoted identifier like amt or Amt or AMT (or even quoted uppercase as "AMT") then those would all be in the data dictionary in the same form and you could refer to it without quotes and with any case - select amt, select Amt``, select AMT`, etc.

But order is a reserved word, as @Joel mentioned, so if you really do (and must) have a table with that name then that would have to be a quoted identifier. I would strongly suggest you call it something else though, like orders.