In Query Builder where a condition has values between two dates, the resultant query adds a semi-colon (;) in the wrong place causing the query syntax to fail.
Steps to replicate.
In Query Builder,
1. drag two tables from 'HR' schema 'EMPLOYEES' and 'DEPARTMENTS'
2. Select FIRST_NAME, LAST_NAME, HIRE_DEPT from EMPLOYEE & DEPARTMENT_NAME from DEPARTMENT
3. Drag HIRE_DEPT over to create a WHERE clause
4. Select 'BETWEEN' and select 'CONSTANT' values
5. Pick two constant values (In my example '03-JAN-90' and '07-DEC-94'
6. Running this query results in 'ORA-00911: invalid character'
If you look at the resulting query, you'll notice that there's an extra ';' at the end of the first 'between date'
/* Formatted on 16-Oct-09 3:08:04 PM (QP5 v5.136.908.31019) */
SELECT EMPLOYEES.FIRST_NAME,
EMPLOYEES.LAST_NAME,
EMPLOYEES.HIRE_DATE,
DEPARTMENTS.DEPARTMENT_NAME
FROM HR.EMPLOYEES, HR.DEPARTMENTS
WHERE (DEPARTMENTS.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
AND (EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID)
AND (EMPLOYEES.HIRE_DATE BETWEEN TO_DATE ('03-JAN-90', 'DD-MON-RR'); ******* semi-colon should not be here
AND TO_DATE('07-DEC-94', 'DD-MON-RR');) **** also semi-colon should be after the ')'
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center