8 Replies Latest reply: Feb 23, 2013 5:00 AM by Nitesh. RSS

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

    963725
      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
          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.
            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
              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
                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.
                  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.
                    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
                      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.
                        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