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.

Lalit Kumar BMessages: 2775Registered: 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.

Lalit Kumar BMessages: 2775Registered: 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.

>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.