Conditional Insert Statement 2

[cuetzpalin]

Hello,

I have a simple question which I can't figure out! I have to insert a row into a table based on a select query results of another table. However, on some rare occasions the data doesn't exist in the select statement table therefore nothing is inserted into the table. I would like to know if you could help me create an "else" scenario.

Here's my basic insert:

INSERT INTO MAIN_TABLE_HEADER
SELECT NAME FROM MEMBERS WHERE MEM_ID = '&MID';

&MID is my variable.

So, if doesn't return any results I would still like it it populate my HEADER table with "Member not found".

Thank you in advance!

- Eddie

 

[SantaMufasa]

Eddie,

There are probably several methods to achieve your desired results. One of the methods follows, which uses the concept of an Oracle OUTER JOIN.

Section 1 -- Sample (MEMBERS and DUAL) data:

select * from members;

MEM_ID NAME
------ ---------
     1 Velasquez
     2 Ngao
     3 Nagayama

select * from dual;

DUMMY
-----
X

1 row selected.

Section 2 -- Empty MAIN_TABLE_HEADER table:

desc MAIN_TABLE_HEADER
 Name                    Null?    Type
 ----------------------- -------- ------------
 NAME                             VARCHAR2(30)

select * from main_table_header;

no rows selected

Section 3 -- "TT_483.sql" to prompt for Member ID and INSERT into table, "main_table_header":

accept mid prompt "Enter value for Member ID: "
INSERT INTO MAIN_TABLE_HEADER 
       SELECT nvl(NAME,'Member not found')
         FROM MEMBERS 
             ,dual
        WHERE MEM_ID(+)||'X' = '&MID'||upper(dummy)
/

Section 4 -- Invocation and execution of script "TT_483.sql":

SQL> @tt_483

Enter value for Member ID: 2

1 row created.

select * from main_table_header;

NAME
------------------------------
Ngao

1 row selected.

SQL> @tt_483

Enter value for Member ID: 30

1 row created.

select * from main_table_header;

NAME
------------------------------
Ngao
Member not found

2 rows selected.

Let us know if this is what you wanted or if you have any questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cuetzpalin]

Would section 3 be enough? I basically just need to do is continue populating the header table when the member is not found in the members table?

thank you!

 

[Dagon]

Why don't you just use PL/SQL ?

begin  
  INSERT INTO MAIN_TABLE_HEADER 
 SELECT NAME FROM MEMBERS WHERE MEM_ID = '&MID';
  if sql%notfound then
    insert into main_table_header (column) value ('Member not found');
  end if;
end;

 

[cuetzpalin]

Thanks guys!