Getting a "InnerException: ORA-00923 FROM keyword not found where expected" error in a CAS-CodePudding

I have this oracle query:

SELECT
  CASE WHEN P.ORGINAL_PROSJEKT_K IS NULL
  THEN RAD_LAGER
  ELSE (
    SELECT PO.RAD_LAGER
    FROM PROSJEKT PO
    WHERE PO.PROSJEKT_K = P.ORGINAL_PROSJEKT_K
  ) END AS RAD_LAGER_ORIGINAL
  FROM PROSJEKT P
  WHERE P.PROSJEKT_K = PROSJEKT_K;

The query gives me an "InnerException: ORA-00923 FROM keyword not found where expected" error, and I cant see where the error is. Anyone who immediately spots something that seems odd?

I tried encapsulating RAD_LAGER_ORIGINAL is quotation marks and changing name to not contain "_".

The syntax does work in SQL developer, and the error appears when called in the code. I am interested in comparing P.PROSJEKT_S value to PROSJEKT_S value at line 15, where PROSJEKT_S is a foreign key to PROSJEKT table (P.PROSJEKT), hence the where clause at the end. "ProsjektLagetAv" uses a different table, which is why .Formula() is called, and "PROSJEKT_K" is mapped to the table present. I am using C# - mapping and constructing as follows:

Map(x => x.ProsjektLagetAv).Formula(@"(SELECT CASE WHEN P.ORGINAL_PROSJEKT_K IS NULL THEN RAD_LAGER ELSE (SELECT PO.RAD_LAGER FROM PROSJEKT PO WHERE PO.PROSJEKT_K = P.ORGINAL_PROSJEKT_K) END AS RAD_LAGER_ORIGINAL FROM PROSJEKT P WHERE P.PROSJEKT_K = PROSJEKT_K)");

Constructor:

[DataMember]
public virtual string ProsjektLagetAv { get; set; }
PROSJEKT_K is always supplied, and is never null.

Map(x => x.ProsjektK, "PROSJEKT_K");
[DataMember]
public virtual string ProsjektK { get; set; }

SOLVED

NHibernate does something strange when mapping, and couldn't find the last FROM keyword. Solved by removing "AS RAD_LAGER_ORIGINAL"

SELECT
  CASE WHEN P.ORGINAL_PROSJEKT_K IS NULL
  THEN RAD_LAGER
  ELSE (
    SELECT PO.RAD_LAGER
    FROM PROSJEKT PO
    WHERE PO.PROSJEKT_K = P.ORGINAL_PROSJEKT_K
  ) END
  FROM PROSJEKT P
  WHERE P.PROSJEKT_K = PROSJEKT_K;

CodePudding user response：

As far as Oracle is concerned, syntax is OK:

SQL> WITH
  2     prosjekt (prosjekt_k, orginal_prosjekt_k, rad_lager)
  3     AS
  4        (SELECT 1, 1, 100 FROM DUAL)
  5  SELECT CASE
  6            WHEN P.ORGINAL_PROSJEKT_K IS NULL
  7            THEN
  8               RAD_LAGER
  9            ELSE
 10               (SELECT PO.RAD_LAGER
 11                  FROM PROSJEKT PO
 12                 WHERE PO.PROSJEKT_K = P.ORGINAL_PROSJEKT_K)
 13         END AS RAD_LAGER_ORIGINAL
 14    FROM PROSJEKT P
 15   WHERE P.PROSJEKT_K = PROSJEKT_K;

RAD_LAGER_ORIGINAL
------------------
               100

SQL>

However, note that where clause in line #15 is probably wrong. That's

WHERE P.PROSJEKT_K = PROSJEKT_K;
                     ----------
                     what is this?

Oracle treats it as if it were where 1 = 1 or no condition at all. If prosjekt_k is a parameter you pass to a procedure (or something similar), you should not use the same name as column name. Use e.g. par_prosjekt_k.

CodePudding user response：

For some reason NHibernate does something strange when mapping, and can't find the last FROM keyword in a single case clause. Solved by removing "AS RAD_LAGER_ORIGINAL"

SELECT
  CASE WHEN P.ORGINAL_PROSJEKT_K IS NULL
  THEN RAD_LAGER
  ELSE (
    SELECT PO.RAD_LAGER
    FROM PROSJEKT PO
    WHERE PO.PROSJEKT_K = P.ORGINAL_PROSJEKT_K
  ) END
  FROM PROSJEKT P
  WHERE P.PROSJEKT_K = PROSJEKT_K;