Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #230364] Wed, 11 April 2007 12:22 Go to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
I have a one dimensional incoming file with record_no
I have three tables Table Stg , Prod and source

create table source
(record varchar2(10),
source varchar2(3));

create table Stg
( record varchar2(10));

create table Prod
(record varchar2(10));

I want to check for each incoming record_no from a file.
If the record number exists in Stg table and not in Prod table then I want to insert that record_no into source table as "New" in source column. If the record exists in table Stg and exists in table Prod, then source as "Ext".

Thanks
Re: SQL Query [message #230369 is a reply to message #230364] Wed, 11 April 2007 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if the record does not exist in Stg table?

Regards
Michel
Re: SQL Query [message #230371 is a reply to message #230369] Wed, 11 April 2007 12:47 Go to previous messageGo to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
If the record does not exist in the staging table then also its an "New" because it is coming in the file as a brand new record.

Thanks
Re: SQL Query [message #230374 is a reply to message #230371] Wed, 11 April 2007 12:57 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into source
select ext.record, 
       case when stg.record is null or prod.record is null then 'New'
            else 'Ext'
       end
from external_table ext, stg, prod
where stg.record (+) = ext.record
  and prod.record (+) = ext.record
/

For external_table table, search for "external table" on Oracle documentation.

Regards
Michel
Previous Topic: Utl_File error
Next Topic: very newb question on creating a table
Goto Forum:
  


Current Time: Mon Dec 05 13:03:02 CST 2016

Total time taken to generate the page: 0.10072 seconds