Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Why am i getting ORA-00909: invalid number of arguments for this querry ?

857480Apr 25 2011 — edited Apr 27 2011
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((d.department_name),(nvl(:P2_REPORT_
SEARCH,d.department_name)))>0 ;
This post has been answered by kendenny on Apr 25 2011
Jump to Answer

Comments

Ora
Try this: 
SELECT d.DEPARTMENT_ID,
d.DEPARTMENT_NAME,
(select count(*) from oehr_employees where department_id = d.department_id) as "Number of Employees",
substr(e.first_name,1,1)||'.'||e.last_name as "Manager Name",
c.COUNTRY_NAME as "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((d.department_name),(nvl(:P2_REPORT_SEARCH,d.department_name)))>0 ;
Edited by: Ora on Apr 25, 2011 5:38 AM
819888
You are not using instr function with correct number of arguments.

Regards
kendenny
Answer
Must be because you have a line break in the middle of p2_report_search.
Works for me without the line break. 
SQL> variable p2_report_search  varchar2(10) := 'D';
SQL> 
SQL> WITH oehr_departments AS
  2    (SELECT 1 department_id, 'D1' department_name
  3           ,1 location_id, 1 manager_id FROM dual)
  4      ,oehr_locations AS
  5    (SELECT 1 location_id, 1 country_id FROM dual)
  6      ,oehr_countries AS
  7    (SELECT 1 country_id, 'US' country_name FROM dual)
  8      ,oehr_employees AS
  9    (SELECT 1 employee_id, 1 department_id, 'Ken' first_name, 'Denny' last_name FROM dual
 10     UNION
 11     SELECT 2 employee_id, 1 department_id, 'John' first_name, 'Smith' last_name FROM dual)
 12  SELECT d.DEPARTMENT_ID,
 13         d.DEPARTMENT_NAME,
 14         (select count(*)from oehr_employees where department_id = d.department_id)
 15            "Number of Employees",
 16          substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
 17          c.COUNTRY_NAME "Location"
 18    FROM OEHR_DEPARTMENTS d,
 19         OEHR_EMPLOYEES e,
 20         OEHR_LOCATIONS l,
 21         OEHR_COUNTRIES c
 22   WHERE d.LOCATION_ID=l.LOCATION_ID
 23     AND l.COUNTRY_ID=c.COUNTRY_ID
 24     AND d.DEPARTMENT_ID=e.department_id
 25     AND d.manager_id=e.employee_id
 26     AND instr((d.department_name),(nvl(:P2_REPORT_SEARCH,d.department_name)))>0 ;
 
DEPARTMENT_ID DEPARTMENT_NAME Number of Employees Manager Name Location
------------- --------------- ------------------- ------------ --------
            1 D1                                2 K.Denny      US
Marked as Answer by 857480 · Sep 27 2020
857480
Thanks ora... made it..!! :)
857480
Thanks Ken... fixed it..!! have a great day too...!!
857480
well what is the difference between the above and yours ora.....? could you pls explain.

thanks
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 25 2011
Added on Apr 25 2011
#error, #sql
6 comments
17,649 views