Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Cool Unanswered: Using a Select Statement Within an If statement

    Hi,
    I am in the process of creating a function that looks like the following:

    if v_function_code = 'P_DeleteBank' then
    if v_field_ref = 'local_acc_no' then
    select local_acc_no, corr_acc_no
    into v_local_acc_no, v_corr_acc_no
    from bank
    where to_char(corr_acc_no) = v_ref1;
    if to_char(v_corr_acc_no) = v_ref1 then
    return(v_local_acc_no);
    else
    return('NONE FOUND');
    end if;
    else
    return(v_ref1);
    end if;

    There is more code in front of the above text and below it, but I am stuck with the code above. I am trying to say if x then if y then select z from n where z = j.

    I get the statement to work, but if the statement is invalid, I can not get the else condition to work correctly. My code gets caught within the select statement. Any help is greatly appreciated!!!!!!!!!!!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If that's the code you try to compile, you won't succeed as there is a syntax error - your "if v_field_ref = 'local_acc_no' then" doesn't have "end if" ...

    Suggested excerpt from your procedure could then be
    PHP Code:
       IF v_function_code 'P_DeleteBank'
       
    THEN
          
    IF v_field_ref 'local_acc_no'
          
    THEN
             SELECT local_acc_no
    corr_acc_no
               INTO v_local_acc_no
    v_corr_acc_no
               FROM bank
              WHERE TO_CHAR 
    (corr_acc_no) = v_ref1;
          
    END IF;   -- you don't have this "end if" statement

          IF TO_CHAR (v_corr_acc_no) = v_ref1
          THEN
             RETURN (v_local_acc_no);
          ELSE
             RETURN ('
    NONE FOUND');
          END IF;
       ELSE
          RETURN (v_ref1);
       END IF; 
    P.S. Colours seem to be awkward, but don't mind them ... mind the code

  3. #3
    Join Date
    Aug 2003
    Posts
    6

    That still does not solve my problem

    When executing the function, I still can not get the "NONE FOUND" else to work. That is my main problem. Any help is greatly appreciated. Here is the updated function:
    ------------------------------------------------------------------------------------create or replace function getRef1(p_ref1 in varchar2, p_ref2 in varchar2, p_function_code in varchar2, p_field_ref in varchar2) return varchar2
    is
    v_ref1 alog.ref1%type;
    v_ref2 alog.ref2%type;
    v_function_code alog.function_code%type;
    v_corr_acc_no bank.corr_acc_no%type;
    v_local_acc_no bank.local_acc_no%type;
    v_field_ref alog.field_ref%type;

    begin
    v_function_code := p_function_code;
    v_ref1 := p_ref1;
    v_ref2 := p_ref2;
    v_field_ref := p_field_ref;

    if v_function_code = 'P_DeleteBank'
    then
    if v_field_ref = 'local_acc_no'
    then
    select local_acc_no, corr_acc_no
    into v_local_acc_no, v_corr_acc_no
    from bank
    where to_char(corr_acc_no) = v_ref1;
    end if;
    if to_char(v_corr_acc_no) = v_ref1
    then
    return(v_local_acc_no);
    else
    return('NONE FOUND');
    end if;

    elsif v_function_code = 'P_InsertAcgr'
    then
    if v_field_ref = 'group_code'
    then
    select local_acc_no, corr_acc_no
    into v_local_acc_no, v_corr_acc_no
    from bank
    where to_char(corr_acc_no) = v_ref2;
    end if;
    if to_char(v_corr_acc_no) = v_ref2
    then
    return(v_local_acc_no);
    else
    return('NONE FOUND');
    end if;
    else
    return(v_ref1);

    end if;
    exception
    when NO_DATA_FOUND then
    return(null);
    end;

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understand what you mean, you'd like to have a string 'NONE FOUND' as a return value.

    If SELECT finds nothing to select, it raises a NO_DATA_FOUND exception which is handled at the end of your function and down there you return NULL.

    If you want to return 'NONE FOUND', you'd have to rewrite the function using additional BEGIN ... END blocks that would encapsulate EXCEPTION handlers and in there you could return 'none found' string.

    Something like:
    PHP Code:
    CREATE OR REPLACE FUNCTION getref1 (
       
    p_ref1            IN   VARCHAR2,
       
    p_ref2            IN   VARCHAR2,
       
    p_function_code   IN   VARCHAR2,
       
    p_field_ref       IN   VARCHAR2
    )
       RETURN 
    VARCHAR2
    IS
       v_ref1            alog
    .ref1%TYPE;
       
    v_ref2            alog.ref2%TYPE;
       
    v_function_code   alog.function_code%TYPE;
       
    v_corr_acc_no     bank.corr_acc_no%TYPE;
       
    v_local_acc_no    bank.local_acc_no%TYPE;
       
    v_field_ref       alog.field_ref%TYPE;
    BEGIN
       v_function_code 
    := p_function_code;
       
    v_ref1 := p_ref1;
       
    v_ref2 := p_ref2;
       
    v_field_ref := p_field_ref;

       IF 
    v_function_code 'P_DeleteBank'
       
    THEN
          BEGIN
             
    IF v_field_ref 'local_acc_no'
             
    THEN
                SELECT local_acc_no
    corr_acc_no
                  INTO v_local_acc_no
    v_corr_acc_no
                  FROM bank
                 WHERE TO_CHAR 
    (corr_acc_no) = v_ref1;
             
    END IF;

             IF 
    TO_CHAR (v_corr_acc_no) = v_ref1
             THEN
                
    RETURN (v_local_acc_no);
             
    END IF;

          
    EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                
    RETURN ('NONE FOUND');
          
    END;

       
    ELSIF v_function_code 'P_InsertAcgr'
       
    THEN
          BEGIN
             
    IF v_field_ref 'group_code'
             
    THEN
                SELECT local_acc_no
    corr_acc_no
                  INTO v_local_acc_no
    v_corr_acc_no
                  FROM bank
                 WHERE TO_CHAR 
    (corr_acc_no) = v_ref2;
             
    END IF;

             IF 
    TO_CHAR (v_corr_acc_no) = v_ref2
             THEN
                
    RETURN (v_local_acc_no);
             
    END IF;

          
    EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                
    RETURN ('NONE FOUND');
          
    END;
       ELSE
          RETURN (
    v_ref1);
       
    END IF;
    END

Posting Permissions

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