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?

Looks correct to me and works properly in my system (Oracle 11.2). BTW: What is your purpose of casting? Do you simply want to round (`round(nvl(col1, col2))`)? — Thorsten Kettner, Jun 16 '16 at 08:08
Didn't succeed to reproduce this. Do you have a runable example? — Florin Ghita, Jun 16 '16 at 08:11
it's an sql that goes into a listbox model in zk. I just don't want to see 12.0, just want to see 12. Btw, it's always gonna be X.0 (so decimals don't make sense, but they just show up) — Tiberiu Dorian Moşescu, Jun 16 '16 at 08:16
What happens if you run the query in SQL*Plus? I.e. is the issue with the query (unlikely) or with zk (whatever that is) interpreting the number in a different way to what you're expecting. I'm guessing the latter, and that you need to get zk to handle it differently. — Boneist, Jun 16 '16 at 08:21
That sounds like a zk problem rather than an Oracle one then? Or possibly a JDBC driver issue. Can you recreate directly in a client (e.g. SQL\*Plus or SQL Developer); and can you add the table definition, more about the query, and more about how you're running it and handling the results? — Alex Poole, Jun 16 '16 at 08:21
@Boneist do you happen to know how I can view the data types of the columns returned by a query in oracle sql developer? — Tiberiu Dorian Moşescu, Jun 16 '16 at 08:27
@TiberiuDorianMoşescu `DESCRIBE table_name;` will show you the columns, data types and whether they are nullable (in SQL/PLUS and SQL Developer). — MT0, Jun 16 '16 at 08:33
@MT0 aye, but I want to do that on the result, aka I have the query select .... from table1 how do I use describe on this (it's not just one table, lots of joins and stuff; it would be nice to have the datatypes of the end result) — Tiberiu Dorian Moşescu, Jun 16 '16 at 08:37
@TiberiuDorianMoşescu You can use [`DUMP()`](https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm) and look up the `Typ` code [here](https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#BABCGCHG). Or use the [`DBMS_SQL` package](https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm). — MT0, Jun 16 '16 at 08:40

MT0 · Answer 1 · 2016-06-16T10:57:49.057

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.

score 1 · Answer 2 · answered Jun 16 '16 at 08:40

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

NVL with CAST in Oracle sql?

2 Answers2