5 Replies Latest reply: Feb 24, 2013 6:31 PM by 993043 RSS

    Query cannot be parsed....

    443864
      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
          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
            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
              Thank you very much Lev!
              • 4. Re: Query cannot be parsed....
                933957
                thank you very much
                • 5. Re: Query cannot be parsed....
                  993043
                  Obrigado muito bom

                  resolveu meu problema