I have a developer needing a report of exploded oracle Bills of Material. Taking the model/item from excel created synonym and joining it against a synonym of an oracle view, we're seeing that the join doesn't get passed through to Oracle. Instead, the view does full table scans to get the information, then the join is used once it's built.

I believe this is expected behavior, but is there a way around it to "drive" the view/report from the excel synonym? Wondering if sql passthru is something that would work in this instance.

This message has been edited. Last edited by: <Kathryn Henning>,

Hi MikeK,

You're assumption is correct. It not possible to pass the join through to the database because Oracle wouldn't be able to "see" the Excel file.

1. How many entries do you expect to have in the Excel document? If it's guaranteed to have less than 1000 you may be in luck. You can create a HOLD file with the "keys" from Excel and then use it to filter on the database table via the IN(FILE) function in WebFOCUS. Having a filter such as: WHERE DB_FIELD IN (EXCEL_KEYS) will cause the Oracle adapter to build a SQL expression such as: WHERE (T1.DB_FIELD IN ('A','B','C',....)) assuming A, B, c, etc. are rows in the EXCEL_KEYS HOLD FILE representing the values you want to query.

The IN operator has a limit of 1000 entries which is why this option is not feasible with bigger files.

If you don't mind some extra coding you can split your Excel file entries into multiple HOLD files with up to 1000 entries each. You can then issue as many TABLE FILE ... WHERE .. IN (FILE) as HOLD files you have using universal concatenation (MORE) or FILEDEF (APPEND to put all of the result sets together. This involves some -interesting REPEAT loop logic but may be an option. The drawback is that you will be making multiple calls to the DB which may or ma not impact performance.

2. Another choice would be to create a temporary table in your Oracle schema, load the Excel content into it so now Oracle could handle the join directly.

3. A variation of #2 would be exporting the Excel document to CSV format, place it in a valid location mapped as an Oracle Directory and define an external table on it.

Many ways to skin the cat but some extra work involved anyway

Thanks for replying!

We currently use option 2, but without any interaction from WebFocus. I think we'll have to do some customization so the user can upload the data to the temp table in oracle, and then be able to run a report in WebFocus off that when needed.