Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: Materialized View Number Datatype Problem

    The script below is uses to build a Materialized View.
    All the fields created with:
    Field1*Pct/100 end up as datatype number but
    the decimals are lost.
    The datatype in the MV is Number with length 0 and no Precision

    Ex:
    table1.CHECK_AMT*(table2.RESPONSIBLE_PERCENT/100) CHECK_AMT,
    125.25*(100/100) = 125

    I have tried Trunc and To_number(To_char) with no success.

    To_char does return the correct value but I need the field to
    remain a number.


    SELECT
    table1.REFERENCE_NUM PAY_REFERENCE_NUM,
    table1.RESV_REC_NUM PAY_RESV_REC_NUM,
    table1.BILLKEY_NUM,
    table1.CHECK_NUM,
    table1.CHECK_DATE,
    table1.CHECK_AMT*(table2.RESPONSIBLE_PERCENT/100) CHECK_AMT,
    table1.PAYEE_NAME,
    table1.TRANSACTION_CODE,
    table1.M_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100) M_SUM_AMT,
    table1.C_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100) C_SUM_AMT,
    table1.E_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100) E_SUM_AMT,
    table1.L_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100) L_SUM_AMT,
    (table1.M_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100))+(table1.C_SUM_AMT*(table2.RESPONSIBLE_PERCEN T/100)) OC_SUM_AMT,
    (table1.E_SUM_AMT*(table2.RESPONSIBLE_PERCENT/100))+(table1.L_SUM_AMT*(table2.RESPONSIBLE_PERCEN T/100)) SS_SUM_AMT,
    table1.USER_GROUP_CODE PAY_USER_GROUP_CODE,
    table1.USER_GROUP_NAME PAY_USER_GROUP_NAME,
    table1.FISCAL_YEAR_CODE PAY_FISCAL_YEAR_CODE,
    table1.ENTRY_DATE PAY_ENTRY_DATE,
    table1.VOID_DATE,
    --
    table2.REC_NUM CPF_REC_NUM,
    table2.DEPT_CODE CPF_DEPT_CODE,
    table2.DEPT_NAME CPF_DEPT_NAME,
    table2.FUND_CODE CPF_FUND_CODE,
    table2.FUND_NAME CPF_FUND_NAME,
    table2.DELETED_YN CPF_DELETED_YN,
    table2.RESPONSIBLE_PERCENT CPF_RESPONSIBLE_PERCENT,
    table2.FUND_AMT CPF_FUND_AMT,
    --
    table3.FUND_ONLY_DESC CPF_FUND_ONLY_DESC,
    --
    table4.REPORTING_LEVEL_2 CPF_REPORTING_LEVEL_2,
    --
    view1.DETAIL_LEVEL_DESC CPF_DETAIL_LEVEL_DESC
    FROM
    view1 view1,
    table4 table4,
    table3 table3,
    table2 table2,
    table5 table5,
    table1 table1
    WHERE
    table1.BILLKEY_NUM = table2.BILLKEY_NUM AND
    table1.REFERENCE_NUM = table5.REFERENCE_NUM AND
    table2.FUND_CODE = table3.HIERARCHY_CODE AND
    table2.DEPT_CODE = table4.DETAIL_LEVEL_CODE AND
    table4.REPORTING_LEVEL_2 = view1.DETAIL_LEVEL_CODE AND
    table2.DELETED_YN = 'N'
    AND table5.var1_status_code IN ('C','O','R')

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    I don't understand why you want to be able to store a number value in a column that is declared as a number with length zero and precision zero ? That way it will not contain values.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    4
    I did not explain the problem very well.

    I am not defining as precision 0 scale 0.

    When I use the create table statement Oracle defines
    the datatype as number but does not calculate/decipher
    the precision or scale of the new field.

    Here is a simpler test script that produces the same weirdness..

    CREATE TABLE NEW_TEST_TABLE
    as
    SELECT
    (100/100)*125.50 as TEST_100,
    (100.00/100.00)*125.50 as TEST_100_00,
    (57/100)*125.50 as TEST_57,
    (57/100)*125.50 as TEST_57_100
    FROM
    MY_OLD_TABLE
    REFERENCE_NUM<100

    Now it gets weirder....
    Look at the table you created
    datatype for each field is Number with no Precision or Scale.

    Now run this:

    select
    test_100,to_char(test_100,'9999.99') ,
    test_100_00,to_char(test_100_00,'9999.99'),
    test_57,to_char(test_57,'9999.99'),
    test_57_100,to_char(test_57_100,'9999.99')
    from New_Test_Table

    The data is in the field??? but the datatype is not defined correctly
    in the Create Table or Create MV process so the value does not
    display correctly unless manipulated with To_char....

    Is there a NLS_param or other Oracle config setting to fix this?

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    I have tested this using DUAL as source table, since I have no knowledge of your source table, and the definition of the new_test_table shows colums with datatype number.
    This means that all columns are floating digits, which means that it can store number with the max length of 38 numbers with the ',' some where between the first and the last figure.

    The values shown when selecting the created row, show the correct numbers :

    TEST_100 TEST_100_00 TEST_57 TEST_57_100
    ---------- ----------- ---------- -----------
    125,5 125,5 71,535 71,535

    So, if this is not so in your situation, can you check if the proper values are returned when selecting them in a normal select and / or selecting them from dual (like i did). This way we can narrow down the possible causes for your problem.

    Hope this helpes
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Posts
    4
    This is the problem - unless I format the data with To_char
    the decimals do not display.

    TEST_57 TO_CHAR(TEST_57,'999.99') TO_CHAR(TEST_57,'999.999')
    71 71.54 71.535


    Need to be able to define the field as number(D,S) when creating
    MV.

    Tried creating a table with this setting and using it to build the
    view - failed because I guess MV still thinks/wants the field to
    be a float.

    Is there a statement to convert from Float to Number?

  6. #6
    Join Date
    Sep 2003
    Posts
    4
    SeaZodiac on Experts Exchange confirmed what you stated and that this is 'probably' not possible in a Materialized View

    "I don't think you can do that in materialized view although you can definitely do it in Table creation.
    But I think you should feel relieved now that your data values are well preserved, i.e., you did not lose any information by this process. "



    Ran the same select in SQL Plus and got:

    SELECT * FROM ALL_NEW_TEST_TABLE

    TEST_100 TEST_100_00 TEST_57 TEST_57_100
    ---------- ----------- ---------- -----------
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535
    125.5 125.5 71.535 71.535

    Fom what I could gather from reading other topics about the NUMBER datatype -
    Oracle defines the field this way since it cannot determine the precision and scale - NUMBER without the p,s is the 'safest' way to go which makes it a NUMBER without p,s is a FLOAT(38,0).

    Just wish I had a little more control since not sure still how this will effect Crystal reports or other parts of our web app.......

    The initial scare came from Toad which was not showing the decimal values.

    I think/hope it is a default setting that can be changed in Toad.

    I am closing this now.

    Thanks for the help.

Posting Permissions

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