?
Solved

Oracle Swap Sub Select In Where ?

Posted on 2012-08-29
10
Medium Priority
?
638 Views
Last Modified: 2012-09-02
I have a procedure called four diffent times. The sql is identicle except for one 'And' (sub-select) statetment at the end. I would like to somehow logically 'swap' the 'And' statement. I tried this at the beginning of the procedure. It doesn't work, but it doesn't error either. I think it gets ignored.

AS
   v_sql    VARCHAR2 (300);
BEGIN
    IF level = 1      
       THEN
         v_sql := ' (SELECT .......))';        
    ELSIF level = 2
     THEN
         v_sql := ' (SELECT .... ))';    
END IF;

INSERT INTO ...
(SELECT ....
 WHERE....
  v_sql);
0
Comment
Question by:pointeman
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38345445
v_sql is just 1 string, which, by pure design of your code is some sql, but sql engine cannot know that.
it will handle v_sql as a variable, trying to evaluate it to a boolean value (true or false) at the place you used it. as string is not empty, it will evaluate as true. hence all you calls to this function will evaluate the same way, indeed the "sql" in v_sql is ignored.


you should put the conditions into the select clause directly

AS
   v_sql    VARCHAR2 (300);
BEGIN
    IF level = 1      
       THEN
         v_sql := ' (SELECT .......))';        
    ELSIF level = 2
     THEN
         v_sql := ' (SELECT .... ))';    
END IF;

INSERT INTO ...
(SELECT ....
 WHERE....
  AND ( ( level = 1 AND  ( ... )   )
         OR ( level = 2 AND ( ... ) )
         OR ( level not in ( 1,2 ) ) 
         )

Open in new window

0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 500 total points
ID: 38345519
use EXECUTE IMMEDIATE sql_stmt

EXECUTE IMMEDIATE 'INSERT INTO ... (SELECT .... WHERE.... ' || v_sql || ')';
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 500 total points
ID: 38345566
AS
   v_sql    VARCHAR2 (300);
BEGIN
    IF level = 1      
       THEN
         v_sql := ' (SELECT .......))';        
    ELSIF level = 2
     THEN
         v_sql := ' (SELECT .... ))';    
END IF;

v_sql := 'insert into ...'||v_sql;
execute immediate v_sql;
Note - be sure to have a space at the end of "your insert into ..." statement.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38348547
or use with

with
  sql1 as
  (select 1 x from dual),
  sql2 as
  (select 2 x from dual),
select
  case
    when a = 1 then sql1.x
    when a = 2 then sql2.x
  end
from sql1, sql2
0
 

Author Comment

by:pointeman
ID: 38350003
I like this idea, but finding large performance hit. Want to make sure I have the brackets correct, multiple OR's must be exactly coded, else they don't run. For the sake of brevity, I have an example below.

Insert Into ...
(Select ...
From...
Where...
And col 2  = 'Jim'
AND ( ( level = 1 AND ID IN ( Select ... ) )
   OR   ( level = 2 AND ID IN ( Select ... ) )
   OR   ( level = 3 AND ID IN ( Select ... ) )
 )
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38350268
yes, that looks good
0
 

Author Comment

by:pointeman
ID: 38350342
Execute Immediate looks cool, but doesn't like using variables like so. I errors saying that it cannot find the 'Select'

CREATE OR REPLACE PROCEDURE "PROC1" (
   p_id   IN NUMBER
AS
   v_sql    VARCHAR2 (300);
BEGIN
    IF level = 1      
       THEN
         v_sql := '(SELECT ID FROM table1 Where ID = p_id)';
    END IF;

 v_sql := v_sql;
    EXECUTE IMMEDIATE v_sql;

INSERT INTO ...
(SELECT ....
 WHERE a.ref_no IN v_sql);
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38350367
execute immediate can use variables, using USING keyword:
http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems017.htm
however, the variables cannot contain sql / part, only values
0
 
LVL 32

Expert Comment

by:awking00
ID: 38351325
This is all wrong.
>>CREATE OR REPLACE PROCEDURE "PROC1" (
   p_id   IN NUMBER
AS
   v_sql    VARCHAR2 (300);  ==> v_sql is nothing more than a string of up to 300 characters
BEGIN
    IF level = 1      ==> Where is level coming from?
       THEN
         v_sql := '(SELECT ID FROM table1 Where ID = p_id)';
The statement above needs to be like -
         v_sql := '(SELECT ID FROM table1 Where ID = '||p_id||')';

    END IF;
 v_sql := v_sql; ==> concatenate the beginning of your insert statement here like -
v_sql := 'INSERT INTO ... '||v_sql;
    EXECUTE IMMEDIATE v_sql; ==> This statement will now do the inserts

INSERT INTO ... ==> So this becomes unnecessary
(SELECT ....  ==> As does this
 WHERE a.ref_no IN v_sql); <<  ==> And this makes no sense since v_sql is simply a string
I suspect that you want some sort of additional filter, but that needs to be created when building v_sql.
I would suggest commenting out the execute immediate statement initially and adding a dbms_output.put_line(v_sql); command, which will show you the insert statement you built dynamically. When that statement looks reasonable, you can then uncomment out the execute immediate statement, re-compile and execute.
0
 

Author Closing Comment

by:pointeman
ID: 38359091
Thx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question