inserting data into table from calculation view

[Former Member]

Hello Experts;

I need to insert data into my 7 fields of HANA tables  from calculation view . scenario is given below

Table have 7 fields clm1, clm2, clm3, clm4, clm5 , clm6, clm7

clm1  will be -> hana time stamp ( when created)

clm2 will be -> hana time stamp ( last updated)

clm 3  will be fetched from calculation view field ( calculation view is existing one)

clm 4  will be fetched from calculation view( calculation view is existing one)

clm 5 will be always null

clm 6 will be having character "HANA"

clm7 will be -> HANA SEQUENCE

I am sure it will be done by procedure but get confuse that is there any other type of procedure in which table type will not be bcz in my case i do not need table type

2) how to call view if i do need to do call my calculation view for inserting data

3) or may be i have to simply select my view via select view statement  and insert in table ( what is syntax plz tell!!)

IMPORTANT POINT IS i have to be careful that if clm3 and clm4 ( hana calculation view fields) is already present in hana table so i need to skip this record from inserting into hana table. Do i need to put cursor logic in my procedure for searching my table  ( becausei need to search table that whether  record with this 2 fields are already present into the table or not)

Waiting for your quick response and help

[sreehari_vpillai · Accepted solution]

Hi Atul,

You can not insert data into a table inside any calculation view (Graphical or script based-you can not even call a write enabled procedure inside a calculation view context)

Either, create a write procedure which accept the data to be inserted or, you can directly issue an insert statements as below, which would read the calculation view and insert the result set into the table. Syntax is as follows

INSERT INTO "MYTABLE" values SELECT "COL1","COL2".."COLn" from "CALCVIEW" where clause .

Sreehari

[Former Member · Accepted solution]

@Sree,

Thanks for ur reply

u r correct , same is my query I need to insert data into table via procedure but data  have to come from existing calculation view ( i do not mean to say that inside calculation view I will insert data  may be my sentence was wrong )

I agree and aware  with your solution but this is very specific,  only 2 column data record is coming from  calculation view while other 5 column also need to insert and what need to insert I have explain above

Please reply on this

Can you please explain in details

will appreciate ur help.

[sreehari_vpillai · Accepted solution]

Atul,

You can directly issue an INSERT statement for the same.Something like below.

Let the sequence name be "myseq" and your calculation view is "MYCALCVIEW".

INSERT INTO "YOURTABLE"

SELECT
CURRENT_TIMESTAMP as "CreatedTime",
CURRENT_TIMESTAMP as "LstUpdated", --Manipulate this
"CALCVIEWCOL1",
"CALCVIEWCOL2",
null,
'HANA' as "COL6", --single quotes
myseq.NEXTVAL as "COL7"
from
"MYCALCVIEW"

WHERE
<Where clause if any>;

Sreehari.

[Former Member · Accepted solution]

Thanks for the reply.

it is not working getting below error

[sreehari_vpillai · Accepted solution]

Put little big picture. Not able to see anything

[Former Member · Accepted solution]

Hello Shree;

i did it

i have to give my calculation view path name , which we will get in SYS_BIC-> COLUMN VIEWS

as per example

_SYS_BIC"."alvira.rd.models/COPYOFCOPYOFCU_PACKNG";

[Former Member · Accepted solution]

[Former Member · Accepted solution]

INSERT INTO "RESDET"."ucles.rd.data::RESULT_PUBLICATION"

     Select

     "RESDET"."ucles.rd.data::SEQ_RESULT_PUBLICATION_UID".NEXTVAL,

      "CANDIDATE_MEASURE_UID",

      "MEASUREDEF_SET_UID",

      "STATUS_CODE",

      "WHEN_PUBLISHED",

      "WHEN_CREATED",

      "WHEN_LAST_UPDATED"

  From "_SYS_BIC"."ucles.rd.models/COPYOFCOPYOFCA_PACKAGING2";

[sreehari_vpillai · Accepted solution]

Ok great. mark the correct answer properly and close the thread .