How to import table definitions from SQL query?

Palermo · Post by **Palermo** » Mon Jul 27, 2015 2:07 pm

Hi,

I have a SQL query. The SQL query consists of several types of join operations and tables. I need to import table definitions from the SQL query, for example, for Netezza connector and DB2 connector. Is it possible?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 27, 2015 4:43 pm

ray.wurlod · Post by **ray.wurlod** » Mon Jul 27, 2015 4:49 pm

You need to import table definitions from the tables accessed by the query. There is no mechanism for importing from the query itself; it contains no other useful metadata than the column names (and even some of those can be generated "on the fly" using AS keyword).

Perhaps you could create a table using some construct that uses the result of the query, then import the metadata from that. However, I am not sure that the databases mentioned in your question support doing that.

Palermo · Post by **Palermo** » Tue Jul 28, 2015 12:34 am

ray.wurlod wrote:You need to import table definitions from the tables accessed by the query. There is no mechanism for importing from the query itself; it contains no other useful metadata than the column names (and e ...

Thanks. Maybe you know Netezza and DB2 tools help to describe data type from a SQL query? For example, DbVisualizer works with DB2 database correctly but with Netezza database incorrectly (cannot identify nullable column when a SQL query contains LEFT JOIN).

Timato · Post by **Timato** » Tue Jul 28, 2015 12:53 am

Maybe try wrapping the SQL query into a database view and import that view via the import > table definitions > odbc table definitions option in the DS Client?

ray.wurlod · Post by **ray.wurlod** » Tue Jul 28, 2015 12:53 am

... and Information Server metadata importers have (currently, at least) no mechanisms for interacting with tools such as DbVisualizer.

Palermo · Post by **Palermo** » Tue Jul 28, 2015 5:29 am

Timato wrote:Maybe try wrapping the SQL query into a database view and import that view via the import > table definitions > odbc table definitions option in the DS Client?

I thought about it but I have no access to create and delete views directly and it's slow.

ray.wurlod wrote:... and Information Server metadata importers have (currently, at least) no mechanisms for interacting with tools such as DbVisualizer

I understand it. I want to say that I need tools in order to see return data types from a SQL query and then I will choose data types, sizes and nullable properties in DataStage manually.