Results 1 to 4 of 4

Thread: nvl problem

  1. #1
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Unanswered: nvl problem

    Hi, I have the following function. I thought that this would return 0 (zero) in case the select statement doesn't return any results, but it doesn't.

    Code:
    CREATE OR REPLACE FUNCTION BFE.GET_CATEGORY_ID
    	   (PCATEGORY_NAME IN VARCHAR2)
    RETURN NUMBER
    AS
      	   VCTG_ID NUMBER;
    BEGIN
      SELECT
        CTG_ID INTO VCTG_ID
      FROM  
        JOHAN.CATEGORIES
      WHERE
        CTG_NAME = PCATEGORY_NAME;
    	
      VCTG_ID := NVL(VCTG_ID, 0);  
      RETURN VCTG_ID; 
    END;
    
    
    
    SELECT GET_CATEGORY_ID('something') FROM DUAL
    What am I doing wrong?
    Johan

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Use the following select statement

    SELECT
    NVL(CTG_ID,0) INTO VCTG_ID
    FROM
    JOHAN.CATEGORIES
    WHERE
    CTG_NAME = PCATEGORY_NAME;

    RETURN VCTG_ID;

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: nvl problem

    Originally posted by jora
    Hi, I have the following function. I thought that this would return 0 (zero) in case the select statement doesn't return any results, but it doesn't.

    Code:
    CREATE OR REPLACE FUNCTION BFE.GET_CATEGORY_ID
    	   (PCATEGORY_NAME IN VARCHAR2)
    RETURN NUMBER
    AS
      	   VCTG_ID NUMBER;
    BEGIN
      SELECT
        CTG_ID INTO VCTG_ID
      FROM  
        JOHAN.CATEGORIES
      WHERE
        CTG_NAME = PCATEGORY_NAME;
    	
      VCTG_ID := NVL(VCTG_ID, 0);  
      RETURN VCTG_ID; 
    END;
    
    
    
    SELECT GET_CATEGORY_ID('something') FROM DUAL
    What am I doing wrong?
    CREATE OR REPLACE FUNCTION BFE.GET_CATEGORY_ID
    (PCATEGORY_NAME IN VARCHAR2)
    RETURN NUMBER
    AS
    VCTG_ID NUMBER;
    BEGIN
    SELECT
    CTG_ID INTO VCTG_ID
    FROM
    JOHAN.CATEGORIES
    WHERE
    CTG_NAME = PCATEGORY_NAME;

    VCTG_ID := NVL(VCTG_ID, 0);
    RETURN VCTG_ID;
    exception
    when no_data_found then
    return 0;
    END;

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    @shelva: unfortunately does your solution not work, but thanks anyway
    @lynden.zhang: thanks for solving my problem
    Johan

Posting Permissions

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