Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: SQL NVL(...) function help!

    Is it possible to have a nested NVL() in the sql select statement.

    Example:
    select.......from..........
    * where nvl(nvl(TOOLNUMBER, CATEGORYID)),TEMPLATENUMBER) = ITEM

    If the toolnumber is null use the categroy id value as the item; if the category id is null use the Tool number value as the item; .....If both the toolnumber and the category id are null use the templatenumber value as the item.

    Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SQL> select nvl(nvl(null,null),'all is null') from dual;
    
    NVL(NVL(NUL
    -----------
    all is null
    You might take the time to write this with CASE or DECODE, as I think it's intent might be clearer when you or someone else revisit the code later.

    -cf

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Or COALESCE of course.

    Why they didn't just let NVL take an arbitrary number of arguments I'll never know.

  4. #4
    Join Date
    May 2005
    Posts
    2

    Yes you can nest but...

    NVL2 is another option -

    nvl2(toolnumber, toolnumber, categoryid) --gimme the toolnumber if it isn't null, gimme the categoryid if the toolnumber is null.

    Nesting is always ok as long as you are returning the same type.

    good function syntax helper:

    http://download-west.oracle.com/docs...tion.htm#91645

Posting Permissions

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