Oracle Case Failure Not An Object Or Ref-CodePudding

I'm getting the failure ORA-22806: not an object or REF when querying

SELECT (CASE WHEN "SHAPE" IS NULL THEN NULL
              ELSE ("SHAPE").SDO_POINT.X END)
FROM (SELECT null AS "SHAPE" FROM DUAL)

in Oracle. It appears Oracle is evaluating the else statement even though the case expression is true. Anyone know a way to make this work? Requirements are to return null if "SHAPE" is null, otherwise return ("SHAPE").SDO_POINT.X. Edit: This also needs to be done without modifying the subquery.

CodePudding user response：

Oracle isn't evaluating the ELSE statement, but it seems it is checking the types of columns and expressions in your query.

I suspect that it starts type-checking your query by type-checking the subquery. The subquery is SELECT null AS "SHAPE" FROM DUAL. While type-checking this subquery Oracle doesn't know what the type the SHAPE column should be, so it chooses a type for it rather than make do without knowing the type for the time being and see if it can find out the real type later on.

For me (Oracle 18c XE), the error goes away if I cast null to SDO_GEOMETRY:

SQL> SELECT (CASE WHEN "SHAPE" IS NULL THEN NULL
  2                ELSE ("SHAPE").SDO_POINT.X END)
  3  FROM (SELECT CAST(null AS SDO_GEOMETRY) AS "SHAPE" FROM DUAL);

(CASEWHEN"SHAPE"ISNULLTHENNULLELSE("SHAPE").SDO_POINT.XEND)
-----------------------------------------------------------


SQL>

I am guessing that your database has a table with a SHAPE column of type SDO_GEOMETRY, but I could of course be wrong.

CodePudding user response：

I forgot to mention another requirement - I can't modify the subquery

@SStrong: this additional requirement boils down to: "my unmodified code has a bug: how can I remove the bug without modifying my code?"

So all you can is put in an intermediary wrapper around the subquery which handles NULL values without applying case, using @lukewoodward suggestion. Something like this:

SELECT (CASE WHEN "SHAPE" IS NULL THEN NULL
             ELSE ("SHAPE").SDO_POINT.X END)
FROM ( select case when "SHAPE" is null then cast(null as sdo_geometry) 
                   else "SHAPE" end as "SHAPE"               
        from  (SELECT null AS "SHAPE" FROM DUAL) --<-- your unmodifiable subquery
)
/

CodePudding user response：

Use CAST in the outer query (and you do not need a CASE expression):

SELECT (CAST(SHAPE AS SDO_GEOMETRY)).SDO_POINT.X AS x
FROM   (SELECT null AS "SHAPE" FROM DUAL)