3 Replies Latest reply: May 2, 2013 11:58 AM by rp0428 RSS

    NVL statement for all datatypes

    bobmagan
      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
          Mahir M. Quluzade
          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
            Billy~Verreynne
            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
              DUPLICATE THREAD!

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