Set up search_path for the ODBC Source
Posted: Tue Feb 14, 2012 8:27 pm
You can add a database schema to your search_path variable so that when you log in you won't have to prefix table names with that schema.
For instance, if I log into Vertica as the user dbadmin and I want to query the employee_zip_dim table in the cognos schema, normally I would have to write the following query in the Toad SQL editor window:
But if I add the cognos schema to my schema search path then I won't have to specify the schema in my query. To do that in Toad, I would edit the ODBC connection and add the following text to the "ConnectionString" field:
If I reconnect I'll only have to type:
For instance, if I log into Vertica as the user dbadmin and I want to query the employee_zip_dim table in the cognos schema, normally I would have to write the following query in the Toad SQL editor window:
Code: Select all
SELECT * FROM cognos.employee_zip_dim;
- ConnSettings=SET SEARCH_PATH TO "$user", cognos, public, v_catalog, v_monitor, v_internal
If I reconnect I'll only have to type:
Code: Select all
SELECT * FROM employee_zip_dim;