Oracle Case statement column ambiguously defined

Question

Below I am trying to use a case statement and as soon as I add the arrowed line, I get this error.

ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause:
*Action: Error at Line: 71 Column: 44

   CASE
     WHEN CUST200.TAX_NUMBER is null THEN 2         
>>>> WHEN CUST200.TAX_NUMBER in ("I/C","BRANCH") THEN 1
     WHEN CUST200.ACCOUNT_NUMBER in (8001,5201) THEN 1
     ELSE 2
   END                                         "IC",

I have tried reformatting the line to other syntax like = ANY ("I/C","BRANCH") THEN 1 but get the same error. Even if I remove line "WHEN CUST200.TAX_NUMBER is null THEN 2" I still get the same error. However, "WHEN CUST200.TAX_NUMBER is null THEN 2" works fine if I remove the offending line.

Use single quotes for string literals and double quotes for column/table identifiers. `"I/C"` is double quoted so it refers to a column name `I/C`, which presumably does not exist, and not a string literal which would be `'I/C'` in single quotes. — MT0, Dec 19 '22 at 12:02
If you do have the quoted identifiers `"I/C"` and `"BRANCH"` then you need to [edit] the question with a [MRE] including the `CREATE TABLE` statements for our tables, the `INSERT` statements for some sample data; and a **complete** SQL statement that we can run that generates your error. Having only a tiny snippet of an incomplete query makes it impossible to debug. — MT0, Dec 19 '22 at 12:06
Yep, if there is a column "I/C" or "BRANCH" in your database, maybe you have it in more than one table and must qualify it, e.g. `CUST200.TAX_NUMBER in (CUST200."I/C", CUST200."BRANCH")`. — Thorsten Kettner, Dec 19 '22 at 12:08
However I look at it... If it is supposed to be a string, it sounds weird that a tax number can be equal to the word 'BRANCH'. And if it is supposed to be a column name, it sounds weird a tax number can be equal to a branch. Maybe you just confused things completely here. (Maybe something along the lines of `WHEN cust200.tax_classification in ('I/C', 'BRANCH') THEN ...`?) — Thorsten Kettner, Dec 19 '22 at 12:13
Many sins in this data base, was built 30 years ago. We are moving to D365 so a new chance to kill the sins of the past. Single quotes are in fact the issue. Thank You all that answered. — Darrin Miller, Dec 19 '22 at 12:23

score -1 · Accepted Answer · answered Dec 19 '22 at 12:01

It is a single quote you should use, not double - doing so, Oracle thinks you're referring to a column name (and you aren't).

SQL> WITH
  2     cust200 (tax_number, account_number)
  3     AS
  4        (SELECT 'I/C', 8001 FROM DUAL
  5         UNION ALL
  6         SELECT NULL, 5201 FROM DUAL)
  7  SELECT CASE
  8            WHEN cust200.tax_number IS NULL THEN 2
  9            WHEN cust200.tax_number IN ('I/C', 'BRANCH') THEN 1
 10            WHEN cust200.account_number IN (8001, 5201) THEN 1
 11            ELSE 2
 12         END res
 13    FROM cust200;

       RES
----------
         1
         2

SQL>

Oracle Case statement column ambiguously defined

1 Answers1