CODEWITHSUNDEEP
sqlteradata
gfuller40
gfuller40

Reputation: 1195

issue setting primary index on volatile table

I have the following query referencing temp tables, I'm having an issue setting the primary index (i_sys_clm)

I'm receiving the error: expecting something between the tab2 and "." If I just use with data primary index (i_sys_clm) on commit preserve rows; I get an error saying it is ambiguous. 

 create volatile table Fin as (
 select tab1.*
 ,tab2.i_sys_clm
 ,tab2.c_sta_clm as "new_status"
 ,tab2.whse_curr_row_ind
 ,tab2.whse_load_ts
 ,(case when tab2.c_sta_clm  is null then 'U' else tab1.c_sta_clm end) bom_status  
 ,tab2.c_sta_clm eom_status

 from tab1
 left outer join tab2
 on tab1.i_sys_clm = tab2.i_sys_clm
 ) with data primary index (tab2.i_sys_clm)  on commit preserve rows;

Upvotes: 0

Views: 11105

Answers (1)

Peter R.
Peter R.

Reputation: 11

When creating an index, the index column name refers to the new table being created, not the source table. To add your index: alias tab2.i_sys_clm in the SELECT statement, and then refer to that alias when creating the index. For example:

 create volatile table Fin as (
 select tab1.*
 ,tab2.i_sys_clm as "i_sys_clm_2"
 ,tab2.c_sta_clm as "new_status"
 ,tab2.whse_curr_row_ind
 ,tab2.whse_load_ts
 ,(case when tab2.c_sta_clm  is null then 'U' else tab1.c_sta_clm end) bom_status  
 ,tab2.c_sta_clm eom_status

 from tab1
 left outer join tab2
 on tab1.i_sys_clm = tab2.i_sys_clm
 ) with data primary index (i_sys_clm_2)  on commit preserve rows;

Upvotes: 1

Related Questions