NVL with CAST in Oracle sql?

Question

I want to do something like:

select 
   cast(nvl(col1, col2) as Integer) col_name
from table1;

cast(col1 as Integer) col_name works (returns as Integer)
nvl(col1, col2) col_name works (returns as Double)

When I try to do both, I get it in the form of double, I think, as if cast did nothing. What is the correct syntax and why doesn't mine work properly?

Answer 1

Too long for a comment:

Oracle Setup:

CREATE TABLE table_name (
  col1 NUMBER(5,2),
  col2 NUMBER(3,0)
);

INSERT INTO table_name
SELECT 12,    12 FROM DUAL UNION ALL
SELECT 12.01, 12 FROM DUAL UNION ALL
SELECT NULL,  12 FROM DUAL;

Getting datatypes of SQL output columns:

Now you can use DUMP():

SELECT col1,
       col2,
       DUMP(col1) as d1,
       DUMP(col2) as d2,
       DUMP( CAST( NVL( col1, col2 ) AS Integer ) ) AS d3
FROM   table_name

And this will give the output:

COL1  COL2 D1                    D2                  D3
----- ---- --------------------- ------------------- -------------------
   12   12 Typ=2 Len=2: 193,13   Typ=2 Len=2: 193,13 Typ=2 Len=2: 193,13
12.01   12 Typ=2 Len=3: 193,13,2 Typ=2 Len=2: 193,13 Typ=2 Len=2: 193,13
        12                       Typ=2 Len=2: 193,13 Typ=2 Len=2: 193,13

As you can see the output for CAST( NVL( col1, col2 ) AS Integer ) generates exactly the same dump value.

However, the data type 2 is used for NUMBER(p,s) and FLOAT(p) columns and DUMP() does not tell you what the scale and precision of the column is (you will have to infer it from the dumped value).

If you want to find that then you need to use the DBMS_SQL package:

DECLARE
  c NUMBER := DBMS_SQL.OPEN_CURSOR;
  d NUMBER;
  n INTEGER;
  rec_tab DBMS_SQL.DESC_TAB;
BEGIN
  DBMS_SQL.PARSE(
    c,
    'SELECT CAST( NVL( col1, col2 ) AS Integer ) FROM table_name',
    DBMS_SQL.NATIVE
  );
  d := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.DESCRIBE_COLUMNS(c,n,rec_tab);
  FOR i IN 1 .. n LOOP
    DBMS_OUTPUT.PUT_LINE(
      rec_tab(i).col_name || ': '
      || rec_tab(i).col_type || ' ('
      || rec_tab(i).col_precision || ', '
      || rec_tab(i).col_scale || ')'
    );
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

Which outputs:

CAST(NVL(COL1,COL2)ASINTEGER): 2 (38, 0)

So the output is of type 2 (i.e. a NUMBER(p,s) or FLOAT(p) type) and has precision of 38 and scale of 0 - exactly what would be expected for an Integer.

Answer 2

Here's a test case that categorically shows that the output of the cast(nvl(col1,col2) as integer) is of the exact same type as that of an integer column:

create table test1 (col1 binary_double, col2 binary_double, col3 integer);

insert into test1 values (1, 2, 3);
insert into test1 values (null, 3, 4);
insert into test1 values (null, null, 5);

commit;

select col1,
       col2,
       col3,
       nvl(col1, col2) nvl_col1_col2,
       cast(nvl(col1, col2) as integer) cast_nvl_col1_col2,
       dump(col1) dump_col1,
       dump(col2) dump_col2,
       dump(col3) dump_col3,
       dump(nvl(col1, col2)) dump_nvl_col1_col2,
       dump(cast(nvl(col1, col2) as integer)) dump_cast_nvl_col1_col2
from   test1;


      COL1       COL2       COL3 NVL_COL1_COL2 CAST_NVL_COL1_COL2 DUMP_COL1                           DUMP_COL2                           DUMP_COL3            DUMP_NVL_COL1_COL2                  DUMP_CAST_NVL_COL1_C
---------- ---------- ---------- ------------- ------------------ ----------------------------------- ----------------------------------- -------------------- ----------------------------------- --------------------
         1          2          3             1                  1 Typ=101 Len=8: 191,240,0,0,0,0,0,0  Typ=101 Len=8: 192,0,0,0,0,0,0,0    Typ=2 Len=2: 193,4   Typ=101 Len=8: 191,240,0,0,0,0,0,0  Typ=2 Len=2: 193,2  
                    3          4             3                  3 NULL                                Typ=101 Len=8: 192,8,0,0,0,0,0,0    Typ=2 Len=2: 193,5   Typ=101 Len=8: 192,8,0,0,0,0,0,0    Typ=2 Len=2: 193,4  
                               5                                  NULL                                NULL                                Typ=2 Len=2: 193,6   NULL                                NULL