Here I used With AS Clause.if i use SELECT query it is working fine but if i use insert query . it gives syntax error. Can we use WITH ....INSERT in SAP HANA?
Code:
WITH t1 as
(
Select
col1,
col2,
col3
from table1),
t2 as
(
select
a.col4,
a.col5,
a.col1,
b.col3
from table2 a
left outer join t1
on a.col1 = b. col1)
insert into table3
select
c.col4,
c.col5,
c.col3
from t2;
In addition to Serban's correct answer, a general workaround for lack of CTE functionality is to create views instead. In your case that could be:
create view t1 as
(select
col1,
col2,
col3
from
table1);
create view t2 as
(select
a.col4,
a.col5,
a.col1,
b.col3
from
table2 a
left outer join t1
on a.col1 = b. col1);
insert into table3
select
c.col4,
c.col5,
c.col3
from t2;