Consider the following rules and guidelines when you work with saved SQL queries:
•If you use a saved SQL query that joins one or more tables with the same column names, the test case fails.
For example, consider the following SQL query that involves a full outer join between two tables EMPLOYEE and DEPARTMENT that are part of the SALES.PUBLIC schema, where two columns have the same name, CITY:
SELECT EMP_ID, NAME, CITY, DEPT_ID, DEPT_NAME, CITY
FROM SALES.PUBLIC.EMPLOYEE
FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT ON EMP_ID = DEPT_ID
To distinguish the conflicting column names, add aliases that the database can refer to while joining the tables as shown in the following query:
SELECT e.EMP_ID, e.NAME, e.CITY as ecity, d.DEPT_ID, d.DEPT_NAME, d.CITY as dcity
FROM SALES.PUBLIC.EMPLOYEE e
FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT d ON e.EMP_ID = d.DEPT_ID
•When you export or import a saved SQL query to a different organization, the validation state is not retrieved. You will need to validate the query again.
•When you move or copy a saved SQL query, the validation state is retained.