Import data via OLEDB: Join multiple tables using custom SQL

[hellind]

We don't have the SAS Connector for Oracle. But we are able to import Oracle tables via OLEDB using Enterprise Guide. These are very normalized tables, and hence we have advanced SQL that joins many tables to produce a single table.

If we were to use OLEDB as shown in 2nd attachment, we would be importing many tables (50+) one by one in EG and using Table Join to get the final table.

We have the SQL code ready. Is there a way for us to execute this SQL via OLEDB in EG? It is possible in JMP, as shown in 1st attachment.

[LinusH]

If you want to use your own SQL (OLEDB specific), you have to use explicit pass through. Which means that first you have to connect to your OLEDB data source, then enclose your SQL in either an execute block (if your SQL does not return any values to the SAS session, which is the case when creating permanent tables in the source database), or in a "from connection to" block, which is refferre3d to in a SAS SQL from statement.

This however does not use any of the built in logic in EG.

You could probably recreate some parts of the SQL by using the Query Builder. it has an option that will create the necessary SQL pass-thru specific code for you.

/Linus