This discussion is archived
5 Replies Latest reply: Feb 24, 2013 4:31 PM by 993043 RSS

Query cannot be parsed....

443864 Newbie
Currently Being Moderated
I am completing the Day application express and I am having a basic problem in Modifying the Department report (p38 and 39 of the PDF documentation).
We're supposed to change the sql code by the following:

SELECT d.DEPARTMENT_ID,
d.DEPARTMENT_NAME,
(select count(*)from oehr_employees where department_id = d.department_id)
"Number of Employees",
substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
c.COUNTRY_NAME "Location"
FROM OEHR_DEPARTMENTS d,
OEHR_EMPLOYEES e,
OEHR_LOCATIONS l,
OEHR_COUNTRIES c
WHERE d.LOCATION_ID=l.LOCATION_ID
AND l.COUNTRY_ID=c.COUNTRY_ID
AND d.DEPARTMENT_ID=e.department_id
AND d.manager_id=e.employee_id
AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_
SEARCH,d.department_name)))>0

When I apply changes I have the following message:
Query cannot be parsed, please check the syntax of your query. (ORA-00909: invalid number of arguments)
Any idea ?
Thanks in advance for your help.
  • 1. Re: Query cannot be parsed....
    TexasApexDeveloper Guru
    Currently Being Moderated
    SELECT d.DEPARTMENT_ID,
    d.DEPARTMENT_NAME,
    (select count(*)from oehr_employees where department_id = d.department_id)
    "Number of Employees",
    substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
    c.COUNTRY_NAME "Location" <- Missing Comma here
    FROM OEHR_DEPARTMENTS d,
    OEHR_EMPLOYEES e,
    OEHR_LOCATIONS l,
    OEHR_COUNTRIES c
    WHERE d.LOCATION_ID=l.LOCATION_ID
    AND l.COUNTRY_ID=c.COUNTRY_ID
    AND d.DEPARTMENT_ID=e.department_id
    AND d.manager_id=e.employee_id
    AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_
    SEARCH,d.department_name)))>0


    Thank you,

    Tony Miller
    Webster, TX
  • 2. Re: Query cannot be parsed....
    le Journeyer
    Currently Being Moderated
    Hi,

    It looks like there is a new line in wrong place:
    SELECT d.DEPARTMENT_ID,
    d.DEPARTMENT_NAME,
    (select count(*)from oehr_employees where department_id = d.department_id)
    "Number of Employees",
    substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
    c.COUNTRY_NAME "Location"
    FROM OEHR_DEPARTMENTS d,
    OEHR_EMPLOYEES e,
    OEHR_LOCATIONS l,
    OEHR_COUNTRIES c
    WHERE d.LOCATION_ID=l.LOCATION_ID
    AND l.COUNTRY_ID=c.COUNTRY_ID
    AND d.DEPARTMENT_ID=e.department_id
    AND d.manager_id=e.employee_id
    AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_  --- <-- !!!
    SEARCH,d.department_name)))>0
    it should be:
    SELECT d.DEPARTMENT_ID,
    d.DEPARTMENT_NAME,
    (select count(*)from oehr_employees where department_id = d.department_id)
    "Number of Employees",
    substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
    c.COUNTRY_NAME "Location"
    FROM OEHR_DEPARTMENTS d,
    OEHR_EMPLOYEES e,
    OEHR_LOCATIONS l,
    OEHR_COUNTRIES c
    WHERE d.LOCATION_ID=l.LOCATION_ID
    AND l.COUNTRY_ID=c.COUNTRY_ID
    AND d.DEPARTMENT_ID=e.department_id
    AND d.manager_id=e.employee_id
    AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_SEARCH,d.department_name)))>0
    Lev

    Edited by: le on Jul 2, 2010 10:14 AM
  • 3. Re: Query cannot be parsed....
    443864 Newbie
    Currently Being Moderated
    Thank you very much Lev!
  • 4. Re: Query cannot be parsed....
    933957 Newbie
    Currently Being Moderated
    thank you very much
  • 5. Re: Query cannot be parsed....
    993043 Newbie
    Currently Being Moderated
    Obrigado muito bom

    resolveu meu problema

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points