Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Oracle Decode Problem

    I am using Oracle 8.1.5. I have a query ath like

    SELECT emp_id, DECODE(emp_age, 50, 'G1', 'G2') emp_age_grp
    FROM emp_personal_info

    NOW THE PROBLEM IS THAT the abov query works fine when executing a s a query. As soon as i place it in a stored procedure all the result are G2 no matter what the emp_age is.

    Any ideas wat could be the problem.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think that moving this code into a stored procedure shouldn't cause it not to work properly. Something else must be wrong.
    Could you post the procedure? This example shows that it works correctly:
    Code:
    SQL> select * from emp_personal_info;
    
        EMP_ID    EMP_AGE
    ---------- ----------
             1         20
             2         50
             3         70
    
    SQL> select emp_id, decode(emp_age, 50, 'G1', 'G2') emp_age_grp
      2  from emp_personal_info;
    
        EMP_ID EM
    ---------- --
             1 G2
             2 G1
             3 G2
    
    SQL> create procedure my_proc is
      2    cursor cur is
      3      select emp_id, decode(emp_age, 50, 'G1', 'G2') emp_age_grp
      4      from emp_personal_info;
      5  begin
      6    for cur_row in cur loop
      7      dbms_output.put_line('ID = ' || cur_row.emp_id ||
      8                           ', age group = ' || cur_row.emp_age_grp);
      9    end loop;
     10  end;
     11  /
    
    Procedure created.
    
    SQL> execute my_proc;
    ID = 1, age group = G2
    ID = 2, age group = G1
    ID = 3, age group = G2
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

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