This discussion is archived
3 Replies Latest reply: May 9, 2012 4:00 PM by 901062 RSS

UNION changes type of field

901062 Newbie
Currently Being Moderated
Statement 1:
SELECT E0380.CardValue
FROM (SELECT E0465.CardValue FROM E0465 UNION ALL
SELECT null AS CardValue FROM E0465) E0380

Statement 2:
SELECT E0465.CardValue FROM E0465

I use OleDbCommand to execute these 2 statements. For statement 1 I use GetDecimal method to get value.
For statement 2 I use GetDouble to get value. The type of field CardValue is FLOAT. Why the type in these 2
statements are different? How to solve this problem?

My Oracle is 10g and connection string is "Provider=OraOLEDB.Oracle;Data Source=EMS;Password=abc;User ID=S000"

Thanks a lot

Edited by: user1095915 on May 8, 2012 10:59 AM
  • 1. Re: UNION changes type of field
    Marcus Rangel Journeyer
    Currently Being Moderated
    The default type for NULL is VARCHAR. Try using to_number(null) instead.
  • 2. Re: UNION changes type of field
    gdarling - oracle Expert
    Currently Being Moderated
    I believe the behavior you're describing is apparent outside of OLEDB, so the database sql forum may be better equipped to assist.

    I tested the following, so we could easily see the resulting datatype from the union:
    SQL> create table floattab(col1 float);
    
    Table created.
    
    SQL> SELECT E0380.col1
      2  FROM (SELECT floattab.col1 FROM floattab UNION ALL
      3  SELECT null AS CardValue FROM dual) E0380;
    
          COL1
    ----------
    
    
    SQL> create table tt as SELECT E0380.col1
      2  FROM (SELECT floattab.col1 FROM floattab UNION ALL
      3  SELECT null AS CardValue FROM dual) E0380;
    
    Table created.
    
    SQL> desc tt;
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------
     COL1                                               NUMBER
    
    SQL> desc floattab;
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------
     COL1                                               FLOAT(126)
    
    SQL>
    If I read the docs correctly, it looks like this is probably expected, but the database folks may be able to comment better since the behavior does not seem related to OLEDB.
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

    Hope it helps,
    Greg
  • 3. Re: UNION changes type of field
    901062 Newbie
    Currently Being Moderated
    I found something. There are some default rules, Oracle will obey these rules to convert data type.
    So I can solve this problem. Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points