Search code examples
sqloracle-databaseplsqlsubquery

Oracle SQL Error : 00909. 00000 - "invalid number of arguments" in SELECT subquery

this is my first time using oracle Database in the company and I encountered this problem.

SELECT w.TABLE_NAME tbl_nm,
  MAX(CASE WHEN  t.partition_range LIKE 'year' THEN SUBSTR((w.PARTITION_NAME),1, LENGTH(w.PARTITION_NAME)-4) ELSE concat(SUBSTR((w.PARTITION_NAME), 1, LENGTH(w.PARTITION_NAME)-6)) END) AS par_nm,
  MAX(CASE WHEN t.partition_range LIKE 'year' THEN SUBSTR((w.PARTITION_NAME), LENGTH(w.PARTITION_NAME)-3,4) ELSE concat(SUBSTR((w.PARTITION_NAME), LENGTH(w.PARTITION_NAME)-5,6)) END) AS par_date,
  t.partition_range par_range,
  t.data_dir data_dir,
  t.index_dir index_dir,
  t.add_range add_range,
  CASE WHEN t.partition_range LIKE 'year' THEN EXTRACT(YEAR FROM systimestamp) ELSE TO_CHAR(systimestamp, 'YYYYMM') END AS cur_date
FROM INFORMATION_SCHEMA.PARTITIONS w, t_partition_manage t
WHERE w.TABLE_NAME = t.TABLE_NAME
GROUP BY w.TABLE_NAME

when I Select tbl_nm, par_nm, par_date, par_range, data_dir, index_dir, add_range, cur_date from INFORMATION_SCHEMA.PARTITIONS and t_partition_manage,

there seem to be a problem around in MAX( ... ) and I don't know what is wrong...

This is the error code

00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

and I'm using SQLDeveloper as a client.

Solution

  • There is a problem with concat as it takes two parameters and You are passing only one parameter. Concat is not required at all in your solution:

    SUBSTR(w.PARTITION_NAME, 1, LENGTH(w.PARTITION_NAME)-6)

    And

    SUBSTR(w.PARTITION_NAME), LENGTH(w.PARTITION_NAME)-5,6)

    Also, you need more columns in GROUP BY as select can not use columns directly which are not in GROUP BY clause or you can use aggregate function.