I am looking to build a Matillion job, for below scenario (simplified for this post)
select * from emp where salary > select min(salary) from emp
I could get min(salary) using aggregator, but in Filter component there is no option to > operator on two columns. One of them have to be fixed value..
Other challenge I am facing is, how to join these two table?
workarounds, 1)I could use SQL component in Matillion but I want to avoid using it.
2) I can use calculator and create a boolen variable for comparision output, then use filter to check, but joining them is still an issue
Could someone pour ideas to do this please? thanks
@anandintexas yes, you can use the join component to achieve this.
Make 2 copies of the "Table Input" component for the same table. Before you join both these tables add a aggregate component for 1 of the flows and derive the min salary.
Table Input 1 --> Aggregate (Min) ----\
Table Input 2 ------------------------------------> Join 1
In the join condition you can apply the filter.
Hello, you can use filter, just put the whole column name into the Value field. Like this:
(MAX_DATA_DATE is a MAX(DATA_DATE) window-partitioned value of the same data.)
I also like this solution. thank you
Great!, thank you. it works!. I overlooked that point in the matillion docs.
