This discussion is archived
3 Replies Latest reply: May 2, 2013 9:58 AM by rp0428 RSS

NVL statement for all datatypes

bobmagan Newbie
Currently Being Moderated
On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is.
ie
nvl(x,'1') or nvl(mydate,sysdate)

thanks
  • 1. Re: NVL statement for all datatypes
    MahirM.Quluzade Guru
    Currently Being Moderated
    bobmagan wrote:
    On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
    Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is.
    ie
    nvl(x,'1') or nvl(mydate,sysdate)

    thanks
    I think , it is very difficult.
    Because if want write own function, you must give data type of value, and function must return same type value.

    If you give data types, then why you not using NVL.

    Regards
    Mahir M. Quluzade
  • 2. Re: NVL statement for all datatypes
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    bobmagan wrote:
    On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
    Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is.
    ie
    nvl(x,'1') or nvl(mydate,sysdate)
    How do you determine the default value when the column is null? Will all null varchar2 columns for example, default to the same string value? (which does not make much sense)

    And you mention "dynamic" - exactly what is dynamic and how?

    Can you post a sample table and trigger DDL of what you want to do?
  • 3. Re: NVL statement for all datatypes
    rp0428 Guru
    Currently Being Moderated
    DUPLICATE THREAD!

    Please mark this thread ANSWERED and continue using your other thread.
    NVL statement for all datatypes