Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Error while using UNION in Store Procedure

    All,

    I would like create a strore procedure using UNION cluase. But it giving compilation error.

    My query is can not we use UNION cluase in Store Procedure?

    Here is the sample code and error msg:

    Code:
    create or replace procedure jd_proc
    as
    vno1 varchar2:=0;
    vno2 number:=0;
    vno3 number:=0;
    begin

    select count(*) into vno1 from emp where deptno=10
    union
    select count(*) into vno2 from emp where deptno in(20,30);

    vno3:=vno1 + vno2;
    end;
    /

    Error Msg:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/2 PL/SQL: SQL Statement ignored
    10/18 PL/SQL: ORA-01744: inappropriate INTO


    Regards,
    JD

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, we can. But not the way you're doing it:
    Code:
    CREATE OR REPLACE PROCEDURE jd_proc
    AS
       vno3   NUMBER := 0;
    BEGIN
       SELECT SUM (vno)
         INTO vno3
         FROM (SELECT COUNT (*) vno
                 FROM EMP
                WHERE deptno = 10
               UNION
               SELECT COUNT (*) vno
                 FROM EMP
                WHERE deptno IN (20, 30));
    
       DBMS_OUTPUT.put_line (vno3);
    END;
    /

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Alternatively, don't use UNION:
    Code:
    select count(*) into vno1 from emp where deptno=10;
    select count(*) into vno2 from emp where deptno in(20,30);
    
    vno3:=vno1 + vno2;

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    or just .. deptno in ( 10, 20 , 30 )

Posting Permissions

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