Home » SQL & PL/SQL » SQL & PL/SQL » EXISTS in the IF statement
EXISTS in the IF statement [message #214708] Wed, 17 January 2007 12:33 Go to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Hi all,

I guess you have suced out by now that I am trying to translate T-SQL stored procedures into PL\SQL.

I am not sure how would one go about translating this:
-- statement written in T-SQL
IF (EXISTS( SELECT TableID
            FROM   Columns INNER JOIN Node ON Node.ColumnID = Columns.ColumnID
           )
    )
    BEGIN
    ......


I have tried to cut and paste and see what happen then Oracle give this error message:
PLS-00103: Encountered the symbol "INNER" when expecting one of the following:       
) , with group having intersect minus start union where    connect 


I am programming against Oracle version 9.2.0.1.0

Thanks,
Paul
Re: EXISTS in the IF statement [message #214720 is a reply to message #214708] Wed, 17 January 2007 14:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following example is a direct response to your question:

SCOTT@10gXE> SET SERVEROUTPUT ON
SCOTT@10gXE> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    SELECT COUNT (*)
  5    INTO   v_count
  6    FROM   emp INNER JOIN dept
  7    ON     emp.deptno = dept.deptno;
  8    IF v_count > 0
  9    THEN
 10  	 DBMS_OUTPUT.PUT_LINE ('It exists');
 11    ELSE
 12  	 DBMS_OUTPUT.PUT_LINE ('It does not exist');
 13    END IF;
 14  END;
 15  /
It exists

PL/SQL procedure successfully completed.

SCOTT@10gXE>


However, I have a feeling that you may be looking for something more like:

SELECT ...
FROM   some_table
WHERE  EXISTS
       (SELECT TableID
        FROM   Columns INNER JOIN Node 
        ON     Node.ColumnID = Columns.ColumnID
        WHERE  some_table.some_column = Columns.some_column); 

Re: EXISTS in the IF statement [message #214726 is a reply to message #214708] Wed, 17 January 2007 15:56 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
I am sorry for wasting your time I should have provide you with the original querry in T-SQL.
-- Original query in T-SQL
UPDATE @tempTable
SET Deleted = 1 
WHERE EXISTS (SELECT TableID
              FROM   Columns INNER JOIN Node 
              ON     Node.ColumnID = Columns.ColumnID
              WHERE  some_table.some_column = Columns.some_column))

Hence, my other question posted earlier about updating temp table. And this is my latest attempt:
-- My attempt in PL\SQL
FOR i IN tbl_tempTable.FIRST .. tbl_tempTable.LAST LOOP
  IF( EXISTS (SELECT TableID
              FROM   Columns INNER JOIN Node 
              ON     Node.ColumnID = Columns.ColumnID
              WHERE  some_table.some_column = tbl_tempTable(i).TableID
              )
     )THEN 
       tbl_tempTable(i).Deleted:=1;
     END IF;
   END LOOP;


I am getting the error with I have posted earlier with the inner identifier.
Re: EXISTS in the IF statement [message #214729 is a reply to message #214726] Wed, 17 January 2007 16:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is invalid syntax in Oracle. You'll have to follow Barbara's example (first SELECT ... INTO a variable, then use this variable in IF statement).
Re: EXISTS in the IF statement [message #214740 is a reply to message #214726] Wed, 17 January 2007 19:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following is a combined response to this post and your other post, since they are related. You are trying to do things the hard way. I have provided three examples below. The first is the closest executable example of what you are trying to do. The second is the closest to what your t-sql code would do. The third is the most efficient way to do things in Oracle PL/SQL. Each of the three examples accomplishes the same thing in different ways. This may not be exactly what you are trying to do, but it is the closest I can guess from the tidbits of code that you have provided. You should be able to get the general idea. If not, then please post your full t-sql code and any related items, such as create table statements and insert statements for sample data. Please read the sticky at the top of the forum for what we expect.

scott@ORA92> -- starting tables and data:
scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         1
         2
         3
         4

scott@ORA92> SELECT * FROM columns
  2  /

   TABLEID   COLUMNID
---------- ----------
         1          1
         2          2
         3          3

scott@ORA92> SELECT * FROM node
  2  /

  COLUMNID
----------
         1
         3

scott@ORA92> -- method 1 (closest executable code to what you are trying to do):
scott@ORA92> CREATE OR REPLACE TYPE EntryTableType AS OBJECT
  2  (
  3  	      TableID  NUMBER,
  4  	      ColumnID NUMBER,
  5  	      ColType  VARCHAR2(100),
  6  	      Deleted  Number
  7  );
  8  /

Type created.

scott@ORA92> CREATE OR REPLACE TYPE EntryTable_Temp_Table AS
  2  	TABLE OF EntryTableType
  3  /

Type created.

scott@ORA92> CREATE OR REPLACE PROCEDURE the_stored_procedure
  2  AS
  3    tbl_Entries EntryTable_Temp_Table := NEW EntryTable_Temp_Table();
  4  BEGIN
  5    FOR r IN
  6  	 (SELECT  1 AS tableid, 1 AS columnid, 'ColType1' AS coltype, NULL AS deleted FROM DUAL
  7  	  UNION ALL
  8  	  SELECT  2, 2, 'ColType2', NULL FROM DUAL
  9  	  UNION ALL
 10  	  SELECT  3, 3, 'ColType3', NULL FROM DUAL)
 11    LOOP
 12  	 tbl_entries.EXTEND;
 13  	 tbl_entries(tbl_entries.LAST) := entrytabletype (null, null, null, null);
 14  	 tbl_entries(tbl_entries.LAST).tableid := r.tableid;
 15  	 tbl_entries(tbl_entries.LAST).columnid := r.columnid;
 16  	 tbl_entries(tbl_entries.LAST).coltype := r.coltype;
 17  	 tbl_entries(tbl_entries.LAST).deleted := r.deleted;
 18    END LOOP;
 19    --
 20    for i in tbl_entries.first .. tbl_entries.count loop
 21  	 for x in
 22  	   (select count(*) cnt
 23  	    from   dual
 24  	    where  exists
 25  		     (SELECT TableID
 26  		      FROM   Columns INNER JOIN Node
 27  		      ON     Node.ColumnID = Columns.ColumnID
 28  		      WHERE  columns.tableid = tbl_entries(i).TableID))
 29  	 loop
 30  	    if ( x.cnt = 1 )
 31  	    then
 32  	      tbl_entries(i).deleted := 1;
 33  	    end if;
 34  	 end loop;
 35    end loop;
 36    --
 37    DELETE FROM a_real_table
 38    WHERE  TableID IN
 39  	      (SELECT tableid
 40  	       FROM   TABLE (CAST (tbl_entries AS entrytable_temp_table))
 41  	       WHERE  deleted = 1);
 42  END the_stored_procedure;
 43  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXEC the_stored_procedure

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         2
         4


scott@ORA92> -- starting tables and data:
scott@ORA92> ROLLBACK
  2  /

Rollback complete.

scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         1
         2
         3
         4

scott@ORA92> SELECT * FROM columns
  2  /

   TABLEID   COLUMNID
---------- ----------
         1          1
         2          2
         3          3

scott@ORA92> SELECT * FROM node
  2  /

  COLUMNID
----------
         1
         3

scott@ORA92> -- method 2 using temporary table
scott@ORA92> -- and procedure to insert, update, and delete:
scott@ORA92> CREATE GLOBAL TEMPORARY TABLE tempTable
  2    (TableID   NUMBER,
  3  	ColumnID  NUMBER,
  4  	ColType   VARCHAR2(15),
  5  	Deleted   Number)
  6  ON COMMIT PRESERVE ROWS
  7  /

Table created.

scott@ORA92> CREATE OR REPLACE PROCEDURE the_stored_procedure
  2  AS
  3  BEGIN
  4    INSERT INTO tempTable
  5    SELECT  1, 1, 'ColType1', NULL FROM DUAL
  6    UNION ALL
  7    SELECT  2, 2, 'ColType2', NULL FROM DUAL
  8    UNION ALL
  9    SELECT  3, 3, 'ColType3', NULL FROM DUAL;
 10    --
 11    UPDATE tempTable
 12    SET    Deleted = 1
 13    WHERE  EXISTS (SELECT TableID
 14  		      FROM   Columns INNER JOIN Node
 15  		      ON     Node.ColumnID = Columns.ColumnID
 16  		      WHERE  tempTable.TAbleID = Columns.TableID);
 17    --
 18    DELETE FROM a_real_table
 19    WHERE  TableID IN
 20  	      (SELECT TableID
 21  	       FROM   tempTable
 22  	       WHERE  deleted = 1);
 23  END the_stored_procedure;
 24  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXEC the_stored_procedure

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         2
         4


scott@ORA92> -- starting tables and data:
scott@ORA92> ROLLBACK
  2  /

Rollback complete.

scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         1
         2
         3
         4

scott@ORA92> SELECT * FROM columns
  2  /

   TABLEID   COLUMNID
---------- ----------
         1          1
         2          2
         3          3

scott@ORA92> SELECT * FROM node
  2  /

  COLUMNID
----------
         1
         3

scott@ORA92> -- method 3 without temporary table
scott@ORA92> -- using procedure with one update statement and inline view
scott@ORA92> -- (temp_tab is the alias for the inline view in this example)
scott@ORA92> CREATE OR REPLACE PROCEDURE the_stored_procedure
  2  AS
  3  BEGIN
  4    DELETE FROM a_real_table
  5    WHERE  TableID IN
  6  	      (SELECT TableID
  7  	       FROM   (SELECT  1 AS TableID, 1 AS ColumnID, 'ColType1' AS Coltype, NULL AS Deleted FROM DUAL
  8  		       UNION ALL
  9  		       SELECT  2 AS TAbleID, 2 AS columnID, 'ColType2' AS coltype, NULL AS deleted FROM DUAL
 10  		       UNION ALL
 11  		       SELECT  3 AS tableid, 3 AS columnid, 'ColType3' AS coltype, NULL AS deleted FROM DUAL) temp_tab
 12  	       WHERE  EXISTS
 13  		      (SELECT TAbleID
 14  		       FROM   Columns INNER JOIN Node
 15  		       ON     Node.ColumnID = Columns.ColumnID
 16  		       WHERE  temp_tab.TAbleID = Columns.TableID));
 17  END the_stored_procedure;
 18  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> EXEC the_stored_procedure

PL/SQL procedure successfully completed.

scott@ORA92> SELECT * FROM a_real_table
  2  /

   TABLEID
----------
         2
         4

scott@ORA92> 

Re: EXISTS in the IF statement [message #214806 is a reply to message #214708] Thu, 18 January 2007 02:54 Go to previous message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Thanks a ton Barbara!! Very Happy

I will try to incooperate the most efficient way to my code see if I can get it to work.

Previous Topic: How to insert milliseconds in date field?
Next Topic: Insertion of long column into clob column
Goto Forum:
  


Current Time: Sun Dec 11 08:02:16 CST 2016

Total time taken to generate the page: 0.17389 seconds