cancel
Showing results for 
Search instead for 
Did you mean: 

How to preserve the Calculation View data into a permanent table with primary key condition checking - using Stored Procedure

Former Member
0 Kudos

Hi,


How to preserve the Calculation View data into a permanent table using Stored Procedure.

Ex: Calculation View having Column1, Column2, Column3, Column4, Column5, Column6 Column7

In which Column1, Column2, Column3, Column4, Column5 makes Distinct (composite key)


Permanent Table having similar structure with columns & same data type of that calculation view

Columns : Column1, Column2, Column3, Column4, Column5, Column6 Column7

Primary key : Column1, Column2, Column3, Column4, Column5 (composite key)

How to preserve the Calculation view data into Permanent table without allowing duplicates.

If i used syntax like Select Columns from Calculation view into permanent table.

First time it entries but when 2nd time new records exists in calculation view, few data already exists in Permanent table in that time it will fail to insert because of primary key...

So it should update the data with Primary key condition, if it not exists then it should insert into permanent table.

& i also tried with UPSERT Statement but not getting correct result.

once i call the stored procedure, data has to pull out from calculation view, If exists it should update the records, else insert the new records into permanent table

Please suggest how to resolve this issue.

Thanks,

Thara


Accepted Solutions (0)

Answers (1)

Answers (1)

sreehari_vpillai
Active Contributor
0 Kudos

Hi Thara,

Ideally UPSERT (UPDATE if keys match, INSERT otherwise ) should work for your scenario. If that is not working(may be you should re check the situation why it is not working), you can try this logic . Write a join statement with not equal condition for all the keys with CALCULATION VIEW and the TABLE. ( on CALC_VIEW.COLUMN1 != TABLE.COLUMN1 && ....) . This join would return you the difference of rows available. Then you can do a blind insert to your table . Performance wise, it is not good, but you can have a try here .

Sree

Former Member
0 Kudos

Hi Sree,

First time table - TESTING_DATA_VIEW is empty

If i tried this syntax in Sql Console Statement executed successfully

but Rows Effected "0"

No records are inserted

PRIMARY KEY (LOCATION_CODE,PLANT_CODE,PRODUCT_CODE,MONTH_CODE,PART_CODE)

Calculation View : CAL_CUST_DATA_LIST

UPSERT TESTING_DATA_VIEW

(LOCATION_CODE,PLANT_CODE,PRODUCT_CODE,MONTH_CODE,PART_CODE,COST)
SELECT A.LOCATION_CODE,A.PLANT_CODE,A.PRODUCT_CODE,A.MONTH_CODE,A.PART_CODE,

A.COST
FROM
  TESTING_DATA_VIEW

INNER JOIN
"_SYS_BIC"."PLANT_LIST/CAL_CUST_DATA_LIST" AS A
ON
A.LOCATION_CODE = TESTING_DATA_VIEW.LOCATION_CODE
AND
A.PLANT_CODE = TESTING_DATA_VIEW.PLANT_CODE
AND
A.PRODUCT_CODE = TESTING_DATA_VIEW.PRODUCT_CODE
AND
A.MONTH_CODE = TESTING_DATA_VIEW.MONTH_CODE
AND A.PART_CODE = TESTING_DATA_VIEW.PART_CODE;

Thanks,

Thara

sreehari_vpillai
Active Contributor
0 Kudos

Hi Thara,


can not blame the system . This is an equijoin as no matches found, it will return 0 records. Change the join conditions with != conditions . 

A.LOCATION_CODE != TESTING_DATA_VIEW.LOCATION_CODE

AND

A.PLANT_CODE != TESTING_DATA_VIEW.PLANT_CODE

AND

A.PRODUCT_CODE != TESTING_DATA_VIEW.PRODUCT_CODE

AND

A.MONTH_CODE != TESTING_DATA_VIEW.MONTH_CODE

AND A.PART_CODE != TESTING_DATA_VIEW.PART_CODE

Execute the select statement alone in a separate console and check if it is returning records. Then proceed.

Sree

Former Member
0 Kudos

Hi Sree,

Definitely i am not blaming any system

initiation is  - stmt written is correct or not.

I came to know the correct syntax.

I should not check the condition at all.

it is working fine with this syntax.

UPSERT TESTING_DATA_VIEW

(LOCATION_CODE,PLANT_CODE,

PRODUCT_CODE,MONTH_CODE,PART_

CODE,COST)
SELECT A.LOCATION_CODE,A.PLANT_CODE,A.PRODUCT_CODE,A.MONTH_CODE,A.PART_CODE,A.COST
  FROM  "_SYS_BIC"."PLANT_LIST/CAL_CUST_DATA_LIST";

Thanks,

Thara

sreehari_vpillai
Active Contributor
0 Kudos

Simple silly. Great that UPSERT is working perfectly .

Sree