My data has migrated to a new schema with some column names changed slightly (e.g. product_name_tx to product_name_code).
This query has multiple sub-queries and unions and I’d like to keep the visualization intact and normally when I edit the SQL code the visualization breaks.
Is there a way I can change the schema for all tables that will keep the visualization?
Then I can go through and select the updated columns rather than changing them in the SQL query text.
Thanks
Sorry. The visualization will lose if change the schema. It is not supported now. However, there is another way that can implement this.
You can change it in the Query tab, use the "CTRL+F" functional keys to replace the original schema with the new schema, you need to check if they are replaced correctly and then click the Visualize button to see the Diagram, and in the Diagram You can see the columns , and you need to map it again. the visualization will lose.
For example, If I want to change TDP_AUTO to TDP_AUTO_2, and also update the TDP_AUTO.TESTORDERS.ORDER_ID_1 to TDP_AUTO_2.TESTORDERS ORDER_ID, I will do this. Is this what you want?
Click Visualize, it will popup cannot modelled. in the Message, Click OK
Go to Diagram, you can see Database is changed to TDP_AUTO_2, but the visualize is gone. Choose the different column, and map again. Click Yes if it popup a message that you manually edited the sql in the Query tab, do you want to override the edited SQL with these changes to the diagram?
Hope this helps
Thanks
Queena
Yes, I’ve already done the ctrl+F method but, as you point out, this breaks the visualization which lowers the usefulness of this feature. I hope that QUEST will work on implementing a fix so that visual queries can more easily be updated without breaking.
Try removing the two fully qualified options in the Query Builder. See below. then open the QB file and make a change, like uncheck a column. This should rewrite the query without any schmea references. Then the current schema from the connection would be used.
Tried this and you’re right, when you select/de-select a column it removes the schema.
However, it does not seem to recognize the new schema or updated tables (e.g. legacy tables had ‘_cd’ suffix on keys and new tables have ‘_code’).
When do the above the schema disappears from the query but the tables do not update to reflect the new schema/table naming.
Then any time I try to run the query (whether I correct the column names in the Query tab or not) I get an error that the table does not exist (see below).
[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42P01] ERROR: relation “table_name” does not exist.
What we really need is a way to point QB to new schemas within a connection. If you right-click the QB name you can change the DB connection but there doesn’t seem to be a way to point to new schema within a connection and then have the tables refreshed for column name changes.
Is there a feature-request area on this site where I can log this (or vote for it as I’m sure this is not the first time this has been an issue for a user)?
Thanks
The Query Bulder was not built like the SQL Editor with Schema/database chooser. When doing this it changes the current schema of the session. There is a send to SQL Editor button when you can open an editor with the SQL and change the schema.
Or you could try this--> keep the option I told you about to not write SQL fully qualified. Open up Object Explorer and change the drop down schema to the one you want. I believe this will also set the current schema.
