Hi,
Please see below picture:
I have million of records in Table 1 and 2. want to merge to output format with related data based on some conditions. I tried with Tester, FeatureMerger, TestFilter etc, can any one suggest best approach?
Solved
Which transformer best suits for oracle non spatial tables data merge with related data?
+1Best answer by fmelizard
This was an interesting one. I think @takashi's solution with the inner joins is very interesting but if it had performance problems on the oracle side, then this solution within workbench would do the trick. Basically, if we get the rows sorted well, and we have a fixed set of things we're looking for (which you've confirmed -- we have 3 values for the SEQUENCE_DESC -- then a workspace like this can do the trick:
The key is to use Variables which maintain their value across rows. For quality purposes, set them up initially with known "unset" values. Then as you encounter each row, set the variable that will hold the value we want to accumulate. When we get the last row in a "set" of rows, retrieve back from those Variables, reset them, and output the aggregated/pivoted row.
Workspace and Sqlite sample data attached. Note I did not use exactly the same attribute names as the original, so you'd have to modify my SQL in the SQLCreator appropriately when you point it over ot Oracle.
+18- Contributor
If both tables are in the same Oracle database I would use the SQLCreator and have the database do the join. That way the indexes in the database can be used and performance is the best.
This transformer does not require you to read the tables using a Reader, but allows a SQL statement to be the source of your workspace.
+17- Supporter
Hi,
Totally agree with @erik_jan about the join part but pivoting the attributes might ba not so easy via sql, so I would create a n:m join via the db and pivot it in fme.
I agree that SQL is a good choice. This SELECT statement might work to do that. I'm not familiar with Oracle SQL specification. Please modify this statement (and correct typos) to fit to Oracle appropriately if necessary.
select a.ASSET_ID, a.SPECIFICATION_NO,
b.SEQUENCE_VALUE as SIZE,
c.SEQUENCE_VALUE as HP,
d.SEQUENCE_VALUE as COMMENT
from Table1 as a
inner join (select * from Table2 where SEQUENCE_DESC='SIZE') as b
on b.ASSET_ID=a.ASSET_ID and b.SPECIFICATION_NO=a.SPECIFICATION_NO
inner join (select * from Table2 where SEQUENCE_DESC='HP') as c
on c.ASSET_ID=a.ASSET_ID and c.SPECIFICATION_NO=a.SPECIFICATION_NO
inner join (select * from Table2 where SEQUENCE_DESC='COMMENT') as d
on d.ASSET_ID=a.ASSET_ID and d.SPECIFICATION_NO=a.SPECIFICATION_NO
+19- Safer
Hi @natrakju -- can you confirm that the SEQUENCE_DESC does not vary dynamically -- that is, you know that there are 3 values in there -- SIZE, HP, and COMMENT that you need turned into columns? That is the trickiest part of this interesting problem, and it would help if those 3 names are fixed and don't change.
Also, it isn't clear why you need to do the join -- there is nothing in the parent table not already in the child. Is it just to select only those rows from the child that have values in the parent?
+1fmelizard wrote:
Hi @natrakju -- can you confirm that the SEQUENCE_DESC does not vary dynamically -- that is, you know that there are 3 values in there -- SIZE, HP, and COMMENT that you need turned into columns? That is the trickiest part of this interesting problem, and it would help if those 3 names are fixed and don't change.
Also, it isn't clear why you need to do the join -- there is nothing in the parent table not already in the child. Is it just to select only those rows from the child that have values in the parent?
+19- Safer
- Best Answer
This was an interesting one. I think @takashi's solution with the inner joins is very interesting but if it had performance problems on the oracle side, then this solution within workbench would do the trick. Basically, if we get the rows sorted well, and we have a fixed set of things we're looking for (which you've confirmed -- we have 3 values for the SEQUENCE_DESC -- then a workspace like this can do the trick:
The key is to use Variables which maintain their value across rows. For quality purposes, set them up initially with known "unset" values. Then as you encounter each row, set the variable that will hold the value we want to accumulate. When we get the last row in a "set" of rows, retrieve back from those Variables, reset them, and output the aggregated/pivoted row.
Workspace and Sqlite sample data attached. Note I did not use exactly the same attribute names as the original, so you'd have to modify my SQL in the SQLCreator appropriately when you point it over ot Oracle.
+18- Contributor
erik_jan wrote:
If both tables are in the same Oracle database I would use the SQLCreator and have the database do the join. That way the indexes in the database can be used and performance is the best.
This transformer does not require you to read the tables using a Reader, but allows a SQL statement to be the source of your workspace.
Select t1.asset_id, t1.specification_no
, (Select t2.sequence_value from table2 t2 where t2.sequence_desc='SIZE' and t2.asset_id = t1.asset_id and t2.specification_no = t1.specification_no) as SIZE
, (Select t2.sequence_value from table2 t2 where t2.sequence_desc='HP' and t2.asset_id = t1.asset_id and t2.specification_no = t1.specification_no) as HP
, (Select t2.sequence_value from table2 t2 where t2.sequence_desc='COMMENT' and t2.asset_id = t1.asset_id and t2.specification_no = t1.specification_no) as COMMENT
From Table1 t1;
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.