Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to implement this procedure (Oracle 11g)
Please help me to implement this procedure [message #601834] Tue, 26 November 2013 04:43 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

My requirement is
Take "stage_number" from SOURCE_TABLE table if the record existed in the CHECK_TABLE
based on the schema name then that record should be insert/update in the TARGET_TABLE.

If the record existed in the TARGET_TABLE it should update else it should insert.

Take "stage_number" from SOURCE_TABLE table if the record does not existed in the CHECK_TABLE
based on the schema name then that record should be insert in the STAGE_TABLE.

SELECT STAN_NUMBER,SID,AMOUNT FROM SOURCE_TABLE;
stan_number  sid  amount
S18032       1     1000
S18033       2     2000
S18034       2     5000
S18035       3     8000
S18036       2     9000

SELECT * FROM CHECK_TABLE;
stan_number tan_code
S18032        T1
S18033        T2

SELECT STAN_NUMBER,SID,AMOUNT,LOC FROM TARGET_TABLE;
  
SELECT * FROM SCHEMA_LOCATIONS_TABLE; 
sid loc  loc_id schema_name
1   US     A1     AOB_AD
2   AS     A2     AOB_NE
3   GT     A3     AOB_CE
4   SG     A4     AOB_DT
5   MB     A5     AOB_GN


TEST CASE 1:
In this test data stan_number S18032 should be checked
in the AOB_AD.CHECK_TABLE(should not check in other schema tables) and the record is existed
then it should be inserted in the TARGET_TABLE.

TEST CASE 2:
In this test data stan_number S18033 should be checked
in the AOB_NE.CHECK_TABLE(should not check in other schema tables) and the record is existed
then it should be inserted in the TARGET_TABLE.

TEST CASE 3:
In this test data stan_number S18034 should be checked
in the AOB_NE.CHECK_TABLE(should not check in other schema tables) and the record is not existed
then it should be inserted in the STAGE_TABLE.

TEST CASE 4:
In this test data stan_number S18035 should be checked
in the AOB_CE.CHECK_TABLE(should not check in other schema tables) and the record is not existed
then it should be inserted in the STAGE_TABLE.

TEST CASE 5:
In this test data stan_number S18036 should be checked
in the AOB_NE.CHECK_TABLE(should not check in other schema tables) and the record is not existed
then it should be inserted in the STAGE_TABLE.

I implemented the merge part but I am not able to implement that CHECK_TABLE and STAGE_TABLE
scenario.

CREATE OR REPLACE PROCEDURE merge_proc
IS
BEGIN
MERGE INTO TARGET_TABLE TARGET
            USING(
                SELECT stan_number,s.sid,amount,loc_id FROM SOURCE_TABLE s,
		SCHEMA_LOCATIONS_TABLE slt
		 where s.sid = slt.sid)
            ) SOURCE
      on (TARGET.stan_number = SOURCE.stan_number
          AND TARGET.sid = SOURCE.sid)
            WHEN MATCHED THEN UPDATE
               SET TARGET.amount = SOURCE.amount
            WHEN NOT MATCHED THEN INSERT
                 (   TARGET.stan_number
                   , TARGET.sid
                   , TARGET.amount
                   , TARGET.loc   
                 ) values
                 (  SOURCE.stan_number
                   , SOURCE.sid
                   , SOURCE.amount
		   , SOURCE.loc_id
  
                 );
END;


Please help me.

Thanks.
Re: Please help me to implement this procedure [message #601836 is a reply to message #601834] Tue, 26 November 2013 05:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1815
Registered: May 2013
Location: World Wide on the Web
Senior Member
No need to complicate the USING clause, rather move the WHERE clause to the matched/not matched clause.

Keep the required tables in the using clause. So, you need SOURCE_TABLE, CHECK_TABLE and SCHEMA_LOCATIONS_TABLE. Specify the ON clause to join these tables and put a conditional operator(where clasue) in the matched/not matched clause.

Have a look at conditional_operations in merge

[Updated on: Fri, 07 March 2014 01:33] by Moderator

Report message to a moderator

Re: Please help me to implement this procedure [message #601838 is a reply to message #601836] Tue, 26 November 2013 05:33 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

In the merge statement how will you check this.
The CHECK_TABLE schema name is not constant.
Could you please provide the sample code.

In this test data stan_number S18032 should be checked
in the AOB_AD.CHECK_TABLE(should not check in other schema tables) and the record is existed
then it should be inserted in the TARGET_TABLE.

In this test data stan_number S18036 should be checked
in the AOB_NE.CHECK_TABLE(should not check in other schema tables) and the record is not existed
then it should be inserted in the STAGE_TABLE.

Please help me.

Thanks.
Re: Please help me to implement this procedure [message #601843 is a reply to message #601838] Tue, 26 November 2013 06:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1815
Registered: May 2013
Location: World Wide on the Web
Senior Member
ajaykumarkona wrote on Tue, 26 November 2013 17:03
The CHECK_TABLE schema name is not constant.

In this test data stan_number S18032 should be checked
in the AOB_AD.CHECK_TABLE(should not check in other schema tables) and the record is existed
then it should be inserted in the TARGET_TABLE.

In this test data stan_number S18036 should be checked
in the AOB_NE.CHECK_TABLE(should not check in other schema tables) and the record is not existed
then it should be inserted in the STAGE_TABLE.


You have CHECK_TABLE in different schemata and you want to use it dynamically, so it is not possible to do that in a static SQL. You need to build a logic for that. So, provide the necessary DDLs and insert statements. Post a test code where you have tried to handle the schema name dynamically.
Re: Please help me to implement this procedure [message #601845 is a reply to message #601838] Tue, 26 November 2013 06:34 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I am posting the DDLS.

CREATE TABLE SOURCE_TABLE
(stan_number VARCHAR2(20),
sid NUMBER,
amount NUMBER);

INSERT INTO SOURCE_TABLE VALUES('S18032',1,1000);
INSERT INTO SOURCE_TABLE VALUES('S18033',2,2000);
INSERT INTO SOURCE_TABLE VALUES('S18034',2,5000);
INSERT INTO SOURCE_TABLE VALUES('S18035',3,8000);
INSERT INTO SOURCE_TABLE VALUES('S18036',2,9000);


CREATE TABLE AOB_AD.CHECK_TABLE
(stan_number VARCHAR2(20),
tan_code VARCHAR2(2));

CREATE TABLE AOB_NE.CHECK_TABLE
(stan_number VARCHAR2(20),
tan_code VARCHAR2(2));

CREATE TABLE AOB_CE.CHECK_TABLE
(stan_number VARCHAR2(20),
tan_code VARCHAR2(2));

CREATE TABLE AOB_DT.CHECK_TABLE
(stan_number VARCHAR2(20),
tan_code VARCHAR2(2));

CREATE TABLE AOB_GN.CHECK_TABLE
(stan_number VARCHAR2(20),
tan_code VARCHAR2(2));


INSERT INTO AOB_AD.CHECK_TABLE VALUES('S18032','T1');
INSERT INTO AOB_NE.CHECK_TABLE VALUES('S18033','T2');
INSERT INTO AOB_CE.CHECK_TABLE VALUES('S18034','T2');

CREATE TABLE TARGET_TABLE
(stan_number VARCHAR2(20),
sid NUMBER,
amount NUMBER,
loc VARCHAR2(4));


CREATE TABLE STAGE_TABLE
(stan_number VARCHAR2(20),
sid NUMBER,
amount NUMBER,
loc VARCHAR2(4));


CREATE TABLE SCHEMA_LOCATIONS_TABLE
(
sid NUMBER,
loc VARCHAR2(4),
loc_id VARCHAR2(2),
schema_name VARCHAR2(10));

INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(1,'US','A1','AOB_AD');
INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(2,'AS','A2','AOB_NE');
INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(3,'GT','A3','AOB_CE');
INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(4,'SG','A4','AOB_DT');
INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(5,'MB','A5','AOB_GN');


PLEASE HELP ME.

Thanks.
Re: Please help me to implement this procedure [message #601895 is a reply to message #601845] Tue, 26 November 2013 19:31 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
>You have CHECK_TABLE in different schemata and you want to use it dynamically, so it is not possible to do that in a static SQL.
I disagree.
You can have multiple static statements & you can control which get executed via IF THEN ELSE constructs.
Re: Please help me to implement this procedure [message #601903 is a reply to message #601895] Wed, 27 November 2013 00:54 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Can you please provide the code for my DDLs.

Thanks.
Re: Please help me to implement this procedure [message #601917 is a reply to message #601903] Wed, 27 November 2013 04:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1815
Registered: May 2013
Location: World Wide on the Web
Senior Member
ajaykumarkona wrote on Wed, 27 November 2013 12:24
Can you please provide the code for my DDLs.


I can't create all those schemata for you, however, here is a code which is for a single schema. You need to use IF-ELSE construct for the different schema names.

SQL> CREATE TABLE SOURCE_TABLE
  2  (stan_number VARCHAR2(20),
  3  sid NUMBER,
  4  amount NUMBER);
 
Table created
SQL> INSERT INTO SOURCE_TABLE VALUES('S18032',1,1000);
 
1 row inserted
SQL> INSERT INTO SOURCE_TABLE VALUES('S18033',2,2000);
 
1 row inserted
SQL> INSERT INTO SOURCE_TABLE VALUES('S18034',2,5000);
 
1 row inserted
SQL> INSERT INTO SOURCE_TABLE VALUES('S18035',3,8000);
 
1 row inserted
SQL> INSERT INTO SOURCE_TABLE VALUES('S18036',2,9000);
 
1 row inserted
SQL> INSERT INTO SOURCE_TABLE VALUES('S19036',2,9000);
 
1 row inserted
SQL> CREATE TABLE CHECK_TABLE
  2  (stan_number VARCHAR2(20),
  3  tan_code VARCHAR2(2));
 
Table created
SQL> INSERT INTO CHECK_TABLE VALUES('S18032','T1');
 
1 row inserted
SQL> CREATE TABLE TARGET_TABLE
  2  (stan_number VARCHAR2(20),
  3  sid NUMBER,
  4  amount NUMBER,
  5  loc VARCHAR2(4));
 
Table created
SQL> CREATE TABLE STAGE_TABLE
  2  (stan_number VARCHAR2(20),
  3  sid NUMBER,
  4  amount NUMBER,
  5  loc VARCHAR2(4));
 
Table created
SQL> CREATE TABLE SCHEMA_LOCATIONS_TABLE
  2  (
  3  sid NUMBER,
  4  loc VARCHAR2(4),
  5  loc_id VARCHAR2(2),
  6  schema_name VARCHAR2(10));
 
Table created
SQL> INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(1,'US','A1','AOB_AD');
 
1 row inserted
SQL> INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(2,'AS','A2','AOB_NE');
 
1 row inserted
SQL> INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(3,'GT','A3','AOB_CE');
 
1 row inserted
SQL> INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(4,'SG','A4','AOB_DT');
 
1 row inserted
SQL> INSERT INTO SCHEMA_LOCATIONS_TABLE VALUES(5,'MB','A5','AOB_GN');
 
1 row inserted

SQL> -- check not match condition for target table
SQL> MERGE INTO TARGET_TABLE TARGET
  2  USING (SELECT src.STAN_NUMBER, SRC.SID, SRC.AMOUNT
  3           FROM CHECK_TABLE CHK, SOURCE_TABLE SRC
  4          WHERE CHK.STAN_NUMBER = SRC.STAN_NUMBER) S
  5  ON (S.STAN_NUMBER = TARGET.STAN_NUMBER)
  6  WHEN MATCHED THEN
  7     UPDATE SET TARGET.AMOUNT = S.AMOUNT
  8  WHEN NOT MATCHED THEN
  9     INSERT
 10        (TARGET.STAN_NUMBER, TARGET.SID, TARGET.AMOUNT)
 11     VALUES
 12        (S.STAN_NUMBER, S.SID, S.AMOUNT);
 
Done

SQL> SELECT * FROM TARGET_TABLE;
 
STAN_NUMBER                 SID     AMOUNT LOC
-------------------- ---------- ---------- ----
S18032                        1       1000 

SQL> -- check match condition for target table
SQL> update Source_TABLE set amount=9999 where stan_number='S18032';
 
1 row updated

SQL> MERGE INTO TARGET_TABLE TARGET
  2  USING (SELECT CHK.STAN_NUMBER, SRC.SID, SRC.AMOUNT
  3           FROM CHECK_TABLE CHK, SOURCE_TABLE SRC
  4          WHERE CHK.STAN_NUMBER = SRC.STAN_NUMBER) S
  5  ON (S.STAN_NUMBER = TARGET.STAN_NUMBER)
  6  WHEN MATCHED THEN
  7     UPDATE SET TARGET.AMOUNT = S.AMOUNT
  8  WHEN NOT MATCHED THEN
  9     INSERT
 10        (TARGET.STAN_NUMBER, TARGET.SID, TARGET.AMOUNT)
 11     VALUES
 12        (S.STAN_NUMBER, S.SID, S.AMOUNT);
 
Done

SQL> SELECT * FROM TARGET_TABLE;
 
STAN_NUMBER                 SID     AMOUNT LOC
-------------------- ---------- ---------- ----
S18032                        1       9999 

SQL> -- insert into stage table when stan_number do not match
SQL> MERGE INTO stage_TABLE stage
  2  USING (SELECT src.STAN_NUMBER, SRC.SID, SRC.AMOUNT
  3           FROM CHECK_TABLE CHK, SOURCE_TABLE SRC
  4          WHERE CHK.STAN_NUMBER <> SRC.STAN_NUMBER) S
  5  ON (S.STAN_NUMBER = stage.STAN_NUMBER)
  6  WHEN MATCHED THEN    UPDATE SET stage.AMOUNT = S.AMOUNT
  7  WHEN NOT MATCHED THEN
  8     INSERT
  9        (stage.STAN_NUMBER, stage.SID, stage.AMOUNT)
 10     VALUES
 11        (S.STAN_NUMBER, S.SID, S.AMOUNT);
 
Done

SQL> SELECT * FROM STAGE_TABLE;
 
STAN_NUMBER                 SID     AMOUNT LOC
-------------------- ---------- ---------- ----
S18036                        2       9000 
S18035                        3       8000 
S18033                        2       2000 
S19036                        2       9000 
S18034                        2       5000 
 


Regards,
Lalit
Re: Please help me to implement this procedure [message #601996 is a reply to message #601917] Thu, 28 November 2013 06:04 Go to previous message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Thanks for your code.

Previous Topic: trim or remove data and spaces
Next Topic: Insert Script for Inserting values from Excel file
Goto Forum:
  


Current Time: Thu Jul 24 21:32:28 CDT 2014

Total time taken to generate the page: 0.11580 seconds