Error mapping data type Oracle and C#

Question

I have an table with ID NUMBER(18) and I have a class with properties public Int64 ID { get; set; } to mapping ID form C# vs Oracle.

My table definition

But I have an error when I get max ID like this:

1. Run query

   SELECT MAX(ID) ID FROM MYTABLE
   

   The system throw an error:

   "Object of type 'System.Decimal' cannot be converted to type 'System.Int64'."

2. But when I run query like this:

   SELECT ID FROM(
                   SELECT ID FROM MYTABLE ORDER BY ID DESC
                 ) WHERE ROWNUM =1
   

   It OK.

I don't know why (1) throw error, and why (2) done?

What is the difference between datatype (1) and (2)?

Answer 1

Possible scenario 1:

I guess that you have NULL values in table (so your both queries are not equivalent):

SELECT MAX(ID) ID FROM T_00_RQMM  -- aggregate func ignore NULLS

But:

SELECT ID FROM(SELECT ID FROM T_00_RQMM ORDER BY ID DESC) WHERE ROWNUM = 1;  
-- NULL is the max value

DBFiddle Demo

Anyway you should try mapping:

using System.Numerics;
...
public BigInteger ID { get; set; }

---

EDIT:

Possible scenario 2:

It may be problem with data type (then use explicit CAST):

SELECT CAST(MAX(ID) AS NUMBER(18,0)) ID FROM T_00_RQMM 

DBFiddle Demo 2

Full demo:

CREATE TABLE T_00_RQMM (ID NUMBER(18,0));
INSERT INTO T_00_RQMM VALUES(NULL);
INSERT INTO T_00_RQMM VALUES(100);

CREATE TABLE t1 AS SELECT MAX(ID) ID FROM T_00_RQMM;

CREATE TABLE t2 AS SELECT ID FROM(SELECT ID FROM T_00_RQMM ORDER BY ID DESC) 
                                  WHERE ROWNUM = 1;

CREATE TABLE t3 AS SELECT CAST(MAX(ID) AS NUMBER(18,0)) ID FROM T_00_RQMM;


SELECT TABLE_NAME, COLUMN_NAME, DATA_PRECISION, DATA_SCALE
FROM all_tab_cols
where table_name in ('T1', 'T2', 'T3')
ORDER BY Table_name;

Output:

+------------+-------------+----------------+------------+
| TABLE_NAME | COLUMN_NAME | DATA_PRECISION | DATA_SCALE |
+------------+-------------+----------------+------------+
| T1         | ID          | null           | null       |
| T2         | ID          | 18             | 0          |
| T3         | ID          | 18             | 0          |
+------------+-------------+----------------+------------+