hanahana-sql-script

CE Join on columns with different names

I have seen several examples to write CE functions. But, I have not come across any example where a join is performed on columns with different name.

For example:

With SQL:

select    T1.col1, T1.col2, T1.col3 ,  T2.col4, T2.col5, T2.col6
 
from      table1  T1
 
inner join table2 T2
 
on  T1.col3 = T2.col7

WITH CE functions:

table1 = CE_COLUMN_TABLE("SCHEMA"."TABLE1",["col1","col2","col3"]);
 
table2 = CE_COLUMN_TABLE("SCHEMA"."TABLE2",["col4","col5","col6","col7"]);
 
var_out = CE_JOIN(:table1,:table2,[??],[])

Can anyone please help me in completing the join statement.

Thanks Mesh

Solution

  • The trick here to use a projection node. A projection will allow you to rename columns and also filter data. You could do something like:

    prj_1 = CE_PROJECTION(:emp_table,["ID","FNAME", "LNAME", "AGE", "PAY" AS "SALARY"], '"PAY"' > 1000);

prj_2 = CE_PROJECTION(:address,["EMP_ID" AS "ID", "ADDR1", "ADDR2", "ZIP"]);

join_1 = CE_JOIN(:prj_1, :prj_2, ["ID"]);