2

Given this table,

 CREATE TABLE test (
     c01  INT,
     c02  NUMBER(10,5),
     c03  NUMBER(18,10)
 );

I use OCCI (the C++ Library) to execute the following SELECT query:

 select case(c01) when 10 then c02 else c03 end from test;

In this case, the typecode I get is OCCI_SQLT_NUM but the precision and scale both are 0. What does that (0 precision and scale) mean? Is this a bug?

Without knowing the precision/scale, the type-safety, semantics and the correctness of the program is at the stake, as it is not possible to decide whether to treat it as integer or floating-point.

In other words, what is the type of the CASE(x) WHEN y THEN a ELSE b expression? Can a be INT and b be CHAR[32]? I think no. So how is the type computed?

Nawaz
  • 353,942
  • 115
  • 666
  • 851
  • [Is “NUMBER” and “NUMBER(*,0)” the same in Oracle?](http://stackoverflow.com/questions/28207708/is-number-and-number-0-the-same-in-oracle). Maybe helpful. – badola Dec 22 '16 at 09:13
  • @badola: No. This case is different. – Nawaz Dec 22 '16 at 09:16
  • Hm, maybe it's supposed to represent `NUMBER` without scale and precision explicitly specified? Try creating a function which returns `number`, have it return 1, select it from dual and see if you get the same type. As for your `case` question, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm which says all of `return_expr`s must have same datatype or all be numeric; you'll get ora-00932 (datatype mismatch) otherwise. It's also possible that `else NULL` is interfering with the resulting `case` datatype. – Timekiller Dec 22 '16 at 13:22
  • @Timekiller: `NULL` is not causing any issue here. Even if you write `then c02 else c03`, the precision/scale are `0`. – Nawaz Dec 22 '16 at 13:51

1 Answers1

2

In a CASE expression, the data types of the expressions in the THEN and ELSE clauses must be compatible. So you can't have a NUMBER in the THEN clause and then a DATE or VARCHAR2 in the ELSE clause.

However, precision and scale are not part of the data type. It is better to think of precision and scale as constraints on the values that are allowed in a table. NUMBER(10,2) is the same data type as NUMBER, but with the constraint that there should be no more than ten total digits, including the two reserved for the decimal part, and no more than two after the decimal point. (Then, inserting 5.333 will still work - but only because Oracle will automatically and without warning round this number to fit into the column... but if you gave it the input 123456123456, there's no "rounding" that will make it fit so the insert will fail). The data type of your CASE expression is simply NUMBER. (Incidentally, in Oracle SQL, even INT is a constraint - Oracle DOES NOT DO integer arithmetic!!)

NUMBER(0,0) does not exist in Oracle; if you try to CREATE TABLE test (col NUMBER(0,0)), it will fail. The error message will tell you that the precision (the first number) must be between 1 and 38. So whatever produced NUMBER(0,0) sounds like a bug.

  • Thanks for your answer. I understand that the types in both branches should be compatible. However, when I use `NUMBER(10, 4)` in `THEN`, and `NUMBER(5,2)` in `ELSE`, then my question is: what would be precision/scale of the result? Oracle seems to tell me `NUMBER(0,0)`, which doesn't make sense. – Nawaz Dec 22 '16 at 15:39
  • 1
    I am not familiar with "typecode" and all the other stuff you write about. How does Oracle tell you that? I did some testing (I used exactly your table def, inserted some values, and created a new table "as (select CASE_EXPRESSION)" - the column type is NUMBER with no precision and scale. If you check `USER_TAB_COLUMNS` you see that both precision and scale are `NULL`, not 0 (the number zero). Perhaps something, somewhere, changes `NULL` to `0`? Not Oracle though, I don't think it is Oracle that does that. –  Dec 22 '16 at 15:43
  • And, again, the result of the CASE expression has NO pre-determined precision and scale! Those only make sense as constraints, they are not part of the "data type". The data type is an Oracle NUMBER - which is very different from what you are used to in C/C++; it is not a float or double or long double, it is an entirely different data type. –  Dec 22 '16 at 15:44
  • In C/C++, "data type" makes sense for memory allocation (and other things, but let's focus on that). In Oracle, all NUMBER expressions are allocated exactly the same way, with or without precision and scale. You can't use precision or scale to tell Oracle to allocate more or less memory (or storage space) - they are all allocated the same way as if there was no precision or scale specified. Precision and scale are really just "constraints" (in the technical meaning of that word in a database). –  Dec 22 '16 at 15:47
  • Oracle OCCI C++ API tells me the typecode and precision/scale. – Nawaz Dec 22 '16 at 17:14