隨筆-26  評論-13  文章-46  trackbacks-0

          In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

          The syntax for the NVL function is:

          NVL( string1, replace_with )

          string1 is the string to test for a null value.

          replace_with is the value returned if string1 is null.


          Example #1:

          select NVL(supplier_city, 'n/a')
          from suppliers;

          The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.


          Example #2:

          select supplier_id,
          NVL(supplier_desc, supplier_name)
          from suppliers;

          This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.


          Example #3:

          select NVL(commission, 0)
          from sales;

          This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.


          Frequently Asked Questions


          Question:? I tried to use the NVL function through VB to access Oracle DB.

          To be precise,

          select NVL(DIstinct (emp_name),'AAA'),................ from.................

          I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

          Answer:? It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

          select distinct NVL(emp_name, 'AAA')
          from employees;

          Hope this helps!


          Question:? Is it possible to use the NVL function with more than one column with the same function call?? To be clear, if i need to apply this NVL function to more than one column like this:

          NVL(column1;column2 ...... , here is the default value for all )

          Answer:? You will need to make separate NVL function calls for each column. For example:

          select NVL(table_name, 'not found'), NVL(owner, 'not found')
          from all_tables;

          posted on 2006-04-05 13:38 似水流年 閱讀(651) 評論(0)  編輯  收藏 所屬分類: Oracle
          主站蜘蛛池模板: 武宣县| 达孜县| 资阳市| 凤冈县| 南溪县| 平乐县| 东宁县| 昭通市| 图木舒克市| 东安县| 报价| 清水河县| 东兰县| 广安市| 淮滨县| 昆明市| 濮阳县| 永年县| 莎车县| 水城县| 三明市| 大名县| 老河口市| 平邑县| 读书| 洪江市| 三原县| 菏泽市| 申扎县| 阳高县| 青海省| 高淳县| 米易县| 永济市| 奉节县| 裕民县| 肃宁县| 柳河县| 海阳市| 广州市| 柳州市|