Page 1 of 1
How to import table definitions from SQL query?
Posted: Mon Jul 27, 2015 2:07 pm
by Palermo
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?
Posted: Mon Jul 27, 2015 4:43 pm
by ray.wurlod
No.
Posted: Mon Jul 27, 2015 4:49 pm
by ray.wurlod
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.
Posted: Tue Jul 28, 2015 12:34 am
by Palermo
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).
Posted: Tue Jul 28, 2015 12:53 am
by Timato
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?
Posted: Tue Jul 28, 2015 12:53 am
by ray.wurlod
... and Information Server metadata importers have (currently, at least) no mechanisms for interacting with tools such as DbVisualizer.
Posted: Tue Jul 28, 2015 5:29 am
by Palermo
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.