Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    5

    Unanswered: Creating a view on table changes the datatype

    Creating a view on a table changes the data type of a column in the view.

    Code:
    CREATE TABLE SINGLEDAY ( 
    	PRINCIPALID  		NUMBER 	( 9 	) DEFAULT 0     NOT NULL ,
            SINGLEDATE              DATE                        		NOT NULL ,
            UNITS			NUMBER 	( 9 	)           		NOT NULL,
    ) 
    
    
    insert into SINGLEDAY(PRINCIPALID,  SINGLEDATE,UNITS ) values(1,to_date('2004-01-01','YYYY-MM-DD'),100);
    
    insert into SINGLEDAY(PRINCIPALID,  SINGLEDATE,UNITS ) values(1,to_date('2004-01-01','YYYY-MM-DD'),100);
    
    insert into SINGLEDAY(PRINCIPALID,  SINGLEDATE,UNITS ) values(1,to_date('2004-01-02','YYYY-MM-DD'),100);
    
    insert into SINGLEDAY(PRINCIPALID,  SINGLEDATE,UNITS ) values(1,to_date('2004-01-02','YYYY-MM-DD'),100);
    
    
    CREATE OR REPLACE VIEW MYSINGLEDAY AS 
    	SELECT PRINCIPALID, SINGLEDATE, SUM(UNITS) UNITS FROM SINGLEDAY GROUP BY SINGLEDATE, PRINCIPALID

    Code:
    SQL> desc SINGLEDAY
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     PRINCIPALID                               NOT NULL NUMBER(9)
     SINGLEDATE                                NOT NULL DATE
     UNITS                                     NOT NULL NUMBER(9)
    
    SQL> desc MYSINGLEDAY
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     PRINCIPALID                               NOT NULL NUMBER(9)
     SINGLEDATE                                NOT NULL DATE
     UNITS                                              NUMBER

    As you can see the column units in the view is now NUMBER instead of NUMBER(9) and the SUM(UNITS) is stored in as 200.0 instead of 200. And, not null is missing!!

    A select * on MYSINGLEDAY shows the units as 200 but internally it is stored as 200.0 as I could see the same when I make a OCI connection from C++, also the following article from Oracle confirms that


    Native Datatypes

    ref:Table 12-1


    Can somebody please help me understand this.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by raghud View Post
    As you can see the column units in the view is now NUMBER instead of NUMBER(9)
    So what?

    The view always returns the datatype that matches the actual values that are present in the columns
    and the SUM(UNITS) is stored in as 200.0 instead of 200
    No it's not.
    The trailing 0 is added when the value is displayed by your application (maybe it takes the column's data defintion in order to format the numbers?)

    And, not null is missing!!
    As I said: the view will reflect the actual data, so there are apparently rows in the view where UNITS IS NULL


    If you really need to have the identical data types (for whatever obscure reason) then try to cast the columns in the view definition. Something like
    Code:
    CAST (SUM(UNITS) as NUMBER(9)) AS UNITS
    but I don't know if that will really change the view's display

  3. #3
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by shammat View Post
    So what?

    The view always returns the datatype that matches the actual values that are present in the columns
    No it's not.
    The trailing 0 is added when the value is displayed by your application (maybe it takes the column's data defintion in order to format the numbers?)
    I could not agree with this.

    Code:
    The value 7,456,123.89 will display as follows
    NUMBER(9)     7456124 
    NUMBER(9,1)   7456123.9
    NUMBER(*,1)   7456123.9    
    NUMBER(9,2)   7456123.89 
    NUMBER(6)    [not accepted exceeds precision] 
    NUMBER(7,-2)  7456100 
    NUMBER        7456123.89 
    FLOAT         7456123.89 
    FLOAT(12)     7456000.0
    Is the converse not true?

    A Value 100 to a column NUMBER storing as 100.0

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You are confusing display and storage. As shammat already told you, add an explicit cast or to_char if you feel you need it.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    yup. it's your display. works fine for me based against your script.
    Keep in mind that it is "NUMBER" on the view because you are using an aggregate function (SUM) and not the column value.

    Code:
    SQL> CREATE OR REPLACE VIEW MYSINGLEDAY AS
      2     SELECT PRINCIPALID, SINGLEDATE, SUM(UNITS) UNITS FROM SINGLEDAY GROUP BY SINGLEDATE, PRINCIPALID;
    
    View created.
    
    SQL> select * from MYSINGLEDAY;
    
    PRINCIPALID SINGLEDATE        UNITS
    ----------- ------------ ----------
              1 010104 00:00        200
              1 010204 00:00        200
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The real question is, why raghud thinks the whole thing is a problem.
    The view contains the data that is returned from the SELECT statement.
    So what is the problem if the view has a slightly different "definition" than the table?

  7. #7
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by artacus72 View Post
    You are confusing display and storage. As shammat already told you, add an explicit cast or to_char if you feel you need it.
    I might be confusing display and storage. I agree. But, the confusion is because of the data type of the view. The output SELECT statement of the view definition shows the data type as NUMBER(9) but once the view is created it is changed to NUMBER.

    I am sorry, I am wasting your time on this; but, I would like to understand this.

  8. #8
    Join Date
    May 2010
    Posts
    5
    Quote Originally Posted by shammat View Post
    The real question is, why raghud thinks the whole thing is a problem.
    The view contains the data that is returned from the SELECT statement.
    So what is the problem if the view has a slightly different "definition" than the table?

    The whole thing is a problem because my application (C++) interprets the column as double ( because I think it is stored as 200.0 instead of 200) which is not. This might be a slight deviation but DB2 does not have this problem and I got to have a code which checks the DB type just for this. Did I justify my problem?

  9. #9
    Join Date
    May 2010
    Posts
    5

    Solution.

    This is (one of) the solution:

    Code:
    CREATE OR REPLACE VIEW MYSINGLEDAY AS 
    	SELECT PRINCIPALID, SINGLEDATE, CAST(SUM(UNITS) AS NUMERIC(9)) UNITS FROM SINGLEDAY GROUP BY SINGLEDATE, PRINCIPALID
    Code:
    SQL> desc SINGLEDAY
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     PRINCIPALID                               NOT NULL NUMBER(9)
     SINGLEDATE                                NOT NULL DATE
     UNITS                                     NOT NULL NUMBER(9)
    
    SQL> desc MYSINGLEDAY
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     PRINCIPALID                               NOT NULL NUMBER(9)
     SINGLEDATE                                NOT NULL DATE
     UNITS                                              NUMBER(9)

    Data type of UNITS in the view and table are same. This is what I wanted. I still believe my observation that 100 is stored as 100.0 in the database when the data type is NUMBER.

    Shammat et al , thanks for your time and help.

    Regards
    -Raghu
    Last edited by raghud; 05-12-10 at 02:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •