Join 2 tables with a condition based on max date from the third table

[Anonymous]

Hi

I have a requirement to create a Big Data batch job which gets data from Table A[ id, customer_id, order_id, product_id,dateid]

TableB [orderid, orderdate, orderdesc,shippingaddressid,productid,employeeid,posid]

My requirement is to get the inner join data from TableA and TableB and whose orderdate is greater than the last orderdate in destination table Table C.

Table C contains the last orderdate inserted and I want the bigdata job to extract all data from Table B whose orderdate is greater than the max(orderdate) TABLEC.

In other words:

insert into c

select a.*,b*

from a inner join b

on a.orderid=b.orderid

where b.orderdate >(select max(orderdate) from c)

The above is an example and does not reflect the actual data model but is similar in terms of setup.

I am using a tmap to join a and b but cannot filter how to use tmap to introduce the where clause from table c.

Thanks for your help.

Sam

[fdenis]

are you on only one db or did you have multiple sources?
began by creating a hashmap with max(date) of c then add it on your tmap.

[Anonymous]

Hi

Its on the same db, but its a big data job.tHashMap is not available in the BIGData pallette or I couldnt find.

Thanks for your response though.

Thanks!

[fdenis]

if all your data are on the same db it will be quickest to us sql or plsql proc.
on tqlend you can activate thmap on:
file
edit project property
Designer
Settings
TEchnical

you can also use file or table on your db