Thursday, June 26, 2014

1 + NULL = NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:
 
SQL> select 1 from dual
  2  /
 
         1
----------
         1
 
SQL> select nvl(null,'null') from dual
  2  /
 
NVL(NULL,'NULL')
----------------
null
 
SQL> select nvl(to_char(1+null),'null') from dual
  2  /
 
NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null
 
SQL>

Friday, June 20, 2014

MDSYS.SDO_COORD_AXES

This was tested on Oracle 11.2. If you need to look at the ORDER column in the above-mentioned table, there only seems to be one way to do it i.e. in upper case and surrounded by double quotes as shown below. I guess this is because ORDER is a reserved word:
 
SQL> desc mdsys.sdo_coord_axes
Name                       Null?    Type
-------------------------- -------- ------------------
COORD_SYS_ID               NOT NULL NUMBER(10)
COORD_AXIS_NAME_ID                  NUMBER(10)
COORD_AXIS_ORIENTATION              VARCHAR2(24)
COORD_AXIS_ABBREVIATION             VARCHAR2(24)
UOM_ID                              NUMBER(10)
ORDER                      NOT NULL NUMBER(5)
 
SQL> select max(order) from mdsys.sdo_coord_axes
  2  /
select max(order) from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00936: missing expression
 
SQL> select max("order") from mdsys.sdo_coord_axes
  2  /
select max("order") from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00904: "order": invalid identifier
 
SQL> select max(ORDER) from mdsys.sdo_coord_axes
  2  /
select max(ORDER) from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00936: missing expression
 
SQL> select max("ORDER") from mdsys.sdo_coord_axes
  2  /
 
MAX("ORDER")
------------
           3
 
SQL>