This discussion is archived
8 Replies Latest reply: Feb 23, 2013 3:00 AM by Nitesh. RSS

how to find if  ename is MILLER then it return dname otherwise retun ename

963725 Newbie
Currently Being Moderated
create or replace function akk(p_empno number) return varchar2
is
cursor c1 is
select ename from emp where empno=p_empno;
v varchar2(10);
vd varchar2(10);
begin
select dname into vd from dept where loc='DALLAS';
v:=vd;
open c1;
fetch c1 into v;
close c1;
if v='MILLER' then
return(v);
end if;
  • 1. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    user503635 Explorer
    Currently Being Moderated
    Your function will not do as you want...it always returns ename.

    Suppose your emp table has dpt_id to link your dept table...try this
    select decode(ename, 'MILLER', dname,  ename) 
    Into v
    from emp e, dept p
       where e.dpt_id = p.dpt_id 
    And e.empno=p_empno;
    And p.loc='DALLAS';
    No database at hand thus the above not tested.
  • 2. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    Nitesh. Explorer
    Currently Being Moderated
    CREATE OR REPLACE FUNCTION akk (p_empno NUMBER)
    RETURN VARCHAR2
    IS
    CURSOR c1
    IS
    SELECT ename
    FROM emp
    WHERE empno = p_empno;

    v VARCHAR2 (10);
    vd VARCHAR2 (10);
    BEGIN
    SELECT dname
    INTO vd
    FROM dept
    WHERE loc = 'DALLAS';

    OPEN c1;

    FETCH c1
    INTO v;

    CLOSE c1;

    IF v = 'MILLER'
    THEN
    RETURN (v);
    ELSE
    RETURN (vd);
    END IF;
    END;


    Try above modified function and let know results it may fulfill your requirements..

    Rgds,
    Nitklhush.
  • 3. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    963725 Newbie
    Currently Being Moderated
    SQL> CREATE OR REPLACE FUNCTION akk (p_empno NUMBER)
    2 RETURN VARCHAR2
    3 IS
    4 CURSOR c1
    5 IS
    6 SELECT ename
    7 FROM emp
    8 WHERE empno = p_empno;
    9 v VARCHAR2 (10);
    10 vd VARCHAR2 (10);
    11 BEGIN
    12 SELECT dname
    13 INTO vd
    14 FROM dept
    15 WHERE loc = '';
    16 OPEN c1;
    17 FETCH c1
    18 INTO v;
    19 CLOSE c1;
    20 IF v = 'MILLER'
    21 THEN
    22 RETURN (v);
    23 ELSE
    24 RETURN (vd);
    25 END IF;
    26 END;
    27 /

    Function created.

    SQL> declare
    2 x varchar2(10);
    3 begin
    4 x:=akk(7934);
    5 dbms_output.put_line(x);
    6 end;
    7 /
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "SCOTT.AKK", line 12
    ORA-06512: at line 4
  • 4. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    963725 Newbie
    Currently Being Moderated
    Thanks for reply.

    but that was not my answer
  • 5. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    Nitesh. Explorer
    Currently Being Moderated
    Why you giving null to loc column and its working fine here for me .. I am getting output for both as when I am passing correct empno for miller then MILLER is returned and for other employee numbers i am getting return value as dept number ...

    DECLARE
    b VARCHAR2 (200);
    BEGIN
    b := akk (100); ---100 is empno for MILLER as i have given ..
    DBMS_OUTPUT.put_line (b);
    END;




    OUTPUT :

    MILLER







    DECLARE
    b VARCHAR2 (200);
    BEGIN
    b := akk (1000);
    DBMS_OUTPUT.put_line (b);
    END;


    OUTPUT :


    IT


    Now its showing as dept because miller is not mapped to this empno ..
  • 6. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    Nitesh. Explorer
    Currently Being Moderated
    CREATE OR REPLACE FUNCTION akk (p_empno NUMBER)
    RETURN VARCHAR2
    IS
    CURSOR c1
    IS
    SELECT ename
    FROM emp
    WHERE empno = p_empno;

    v VARCHAR2 (10);
    vd VARCHAR2 (10);
    BEGIN
    SELECT dname
    INTO vd
    FROM dept
    WHERE loc = 'RJ';

    OPEN c1;

    FETCH c1
    INTO v;

    IF v IN ('MILLER')
    THEN
    RETURN (vd);
    ELSE
    RETURN (v);
    END IF;

    CLOSE c1;

    END;
    /


    The above written function will work out surely for you .. Just check and let know if any futher clarifications reqd.

    Rgds,
    Nitkhush.
  • 7. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    Christine Schnittker Explorer
    Currently Being Moderated
    Hi,
    >
    create or replace function akk(p_empno number) return varchar2
    is
    cursor c1 is
    select ename from emp where empno=p_empno;
    v varchar2(10);
    vd varchar2(10);
    begin
    select dname into vd from dept where loc='DALLAS';
    v:=vd;
    open c1;
    fetch c1 into v;
    close c1;
    if v='MILLER' then
    return(v);
    end if;
    >

    Sorry to say, but there's so much wrong with this function, I suggest to throw it away completely. The select posted by user503635 (first answer) delivers exactly what you stated in the titel of your post (so why a function at all?)

    I'll try to explain what I mean so maybe you're a bit more aware in the future (I do not say to correct any single point. Throw away the function.)
    <li>coding inconsistency: For one select, you use a cursor - for the other, you select directly into your variable. Why the difference?
    <li>variable declaration: declare them as column types instead of VARCHAR2 with hard coded lengths (e.g. v emp.ename%TYPE;). You'll be grateful once you meet Mr. Whatsyourname
    <li>cursor: Why using a cursor at all? As written before, the select of second post just fulfills the complete requirement in one go
    <li>"performance"/structure: you only need the department name if ename is MILLER. So why fetch it at all if ename is not MILLER?
    <li>confusion: you get dname into vd. Then you move that into v. Then you fetch into v. Unless you check with CURSOR%FOUND, you don't even have an idea if v now contains dname or ename!
    <li>obvious correctness: you want the dname if ename is MILLER. instead you explicitly return result from c1, which is ename, if it is MILLER. You don't specifiy at all what should happen if ename is not MILLER
    <li>...

    If you need a function for whatever reason, just use it as a wrapper for the select in second post.

    //Tine

    PS@Niteshkhush: It would have been nice if you'd have shown the problems with the function instead of just "correcting the result"...
  • 8. Re: how to find if  ename is MILLER then it return dname otherwise retun ename
    Nitesh. Explorer
    Currently Being Moderated
    Sorry dude but i just believed to show the results as person needed the results so i thought to bother about results instead of trying different ways .. Yes Ofcourse its lot of corrections i did with function itself as many basic kind of errors were found ..

    Rgds,
    Nitkhush