Merge/Join: Multi-joins for three flat files?

kattasureshkumar · November 6, 2012 5:30AM

Operating System : 64Bit Windows 7 Enterprise Edition.
Omniscope Version : 2.8-alpha b329 (Oct 5, 2012 11:08:02 PM) r72129
Licensed Version : Till Mar 9, 2013 2:02:41 PM IST

We have two flat files and need to join these two flat files to generate the data.
Listed below the tables and their corresponding columns.
SITES - Siteid, Sitename
TRANSPORT - Sourceid, Destinationid, Quantity , Weight

So the requirement is if we join sites.siteid with transport.sourceid, then we get the Source Name
Then if we join sites.siteid with transport.destinationid , then we get the Destination Name.

Please help me in creating the same in Omniscope file by using the 'Merge/Join' Operation.

If we had considering these flat files as tables and written the join condition, the query looks as mentioned below.
SELECT so.siteid sourceid
, so.sitename sourcename
, de.siteid descid
, de.sitename descname, t.qty, t.weight
FROM temp_sites so
, temp_transport t
, temp_sites de
WHERE de.siteid = t.descid
AND so.siteid = t.sourceid

So now we need to create an iok file based on this query.

Please let me know how to create this step.

kattasureshkumar · November 6, 2012 5:40AM

Initially we had imported both the flat files to Oracle Temp tables, written a custom query as mentioned above and were able to retrieve the desired results. But when running the same in production table with around 1 million data , the custom SQL is not progressing , it is stuck at 8% only for a long time and the Application does not respond after some time.
System Config : 16GB Ram , i3 Quad Core Processor 3.30 GHz , 64Bit Windows 7 Enterprise Edition.

chris · November 6, 2012 5:44AM

Hi - What custom SQL are you using? If you are filtering by a specific field you may need to ensure that field is indexed, for example if you are using a custom query such as "SELECT * FROM SOME_TABLE WHERE SOME_FIELD=SOME_VALUE" you will need to ensure SOME_FIELD is indexed.

kattasureshkumar · November 6, 2012 9:37AM

Hi Chris,

We had created indexes as well for the tables But there is no improvement in the performance.

Please let us know if we can be able to join the flat files as mentioned in the initial post instead of the custom SQL.

chris · November 6, 2012 9:54AM

Hi - Yes, you can join the flat files. Simply drag them into a DataManager workflow, and in the configuration options change the "Cell separator" option to ";". Then add a Merge/Join block

kattasureshkumar · November 7, 2012 7:36AM

Hi Chris,

Yesterday i was giving the wrong join condition while joining the tables.
In this example we have tables A1, A2 & B where A1 & A2 are same tables, joining these tables with two join conditions so that we get source & destination names from the two tables based on the value in table B.

Attaching the *.iok file of this file, just in case if it's helpful

chris · November 7, 2012 8:13AM

Hi - Unfortunately I don't really understand what your asking here. I think it would be best if we could take this off the forums, and instead arrange a phone call/screen-sharing session. I've sent you an email so you have my contact details. Feel free to contact me to arrange this.

kattasureshkumar · November 7, 2012 8:16AM

Sorry Chris if i had confused, in the last post i had mentioned that the issue is resolved and i was able to join the tables successfully . Hence attached the iok file as well.

Welcome!

Categories

Ideas Parade

Tagged