Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Altering the Nullability of a View

    Hi friends,
    I have a View whose column CONTRACT_REFERENCE is Supposed to be Not Null.
    The Column is Not a Nullable Column.
    I need to Change the Nullability to NOT NULL.

    Please Suggest me in Altering this View.

    The query is given Below.

    CREATE OR REPLACE VIEW CENTRAL.ETL_TMP_BG3
    ( CONTRACT_REFERENCE, A400_AND_MORE, LESS_THAN_400)
    AS
    select contract_reference,SUM(less_than_400), sum(a400_and_more)
    from
    (SELECT t2.contract_reference, 0 as less_than_400 ,count(*) a400_and_more
    FROM etl_tmp_BG2 t2
    WHERE TRIM (t2.exposures_account) IS NOT NULL
    AND TRIM (t2.exposures_account) <> 'N/A'
    AND TRIM (t2.facility_number) >= 400
    group by t2.contract_reference
    union

    SELECT t2.contract_reference, count(*) less_than_400 , 0 as a400_and_more
    FROM etl_tmp_BG2 t2
    WHERE ( TRIM (t2.exposures_account) IS NOT NULL
    AND TRIM (t2.exposures_account) <> 'N/A'
    AND TRIM (t2.facility_number) < 400 )
    group by t2.contract_reference
    )
    GROUP BY contract_reference

    _________________________________________________
    My Column definitions are

    Column Name ID Data Type Null?

    A400_AND_MORE 2 NUMBER Y
    LESS_THAN_400 3 NUMBER Y
    CONTRACT_REFERENCE 1 VARCHAR2 (101 Char) Y

    Please help me n Changing the above column CONTRACT_REFERENCE No Nullability 'N'

    Thanks In Advance

    Best Regards
    Juanid Ismail

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    it is not possible to changed it in the view definition, as NOT NULL is taken from base table(s).
    http://www.databasedesign-resource.c...-on-views.html

    However, the query you posted is unnecessarily complex and may be changed to something like this (not tested because it is not possible to reproduce used table from your post):
    Code:
    select contract_reference,
      sum(case when TRIM (t2.facility_number) < 400 then 1 end) LESS_THAN_400,
      sum(case when TRIM (t2.facility_number) >= 400) then 1 end) A400_AND_MORE
    from etl_tmp_BG2 t2
    WHERE TRIM (t2.exposures_account) IS NOT NULL
    AND TRIM (t2.exposures_account) <> 'N/A'
    group by t2.contract_reference;
    (by the way, the columns in view list and query are swapped in your view definition - or do you want to report SUM(LESS_THAN_400) as A400_AND_MORE and vice versa?)

    Wait, you use TRIM (function accepting/returning string) on and compare it with literal 400 (number)? This smells with implicit conversion - either it will return wrong results (as when ordering strings, '50' is greater than '400') or fail when FACILITY_NUMBER contains non-numeric character. Both may happen (not at the same time though). You definitely should fix it by explicitly changing data type of column/compared value (based on required behaviour, which you did not post). Re-designing it should be the best option - if FACILITY_NUMBER can be only numeric, it should have NUMBER data type and no use of TRIM function would be needed.

  3. #3
    Join Date
    Feb 2012
    Posts
    36
    Hey Bro,

    Thanks a Lot For Ur Reply.
    It Helped me for many Question's.
    Even My team Is Happy.

    Thanks Again :-)
    and we have update the query with yours ( with small update by add TO_NUMBER function ) as below :


    select contract_reference,
    sum(case when to_number(t2.facility_number) < 400 then 1 end) LESS_THAN_400,
    sum(case when to_number(t2.facility_number) >= 400 then 1 end) A400_AND_MORE
    from etl_tmp_BG2 t2
    WHERE TRIM(t2.exposures_account)IS NOT NULL
    AND TRIM(t2.exposures_account)<>'N/A'
    group by t2.contract_reference





Posting Permissions

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