Getting error ORA-00909: invalid number of arguments

Question

CREATE VIEW ITCC.release_testcase_count 
AS 
(
SELECT CONCAT(rtm.requirement_id,'-',tct.release_id) AS id,
       rtm.requirement_id AS requirement_id,
       tct.release_id AS release_id, 
       COUNT(tct.release_id) AS testcase_count
from testcase_version tcv 
  INNER JOIN tcr_catalog_tree_testcase tct ON tcv.id = tct.testcase_version_id 
  LEFT JOIN requirement_testcase_mapping rtm ON rtm.testcase_id=tcv.testcase_id
GROUP BY tct.release_id , rtm.requirement_id
);

same query is working for ms sql and my sql without any syntax error. i want to execute it in oracle as well but i am getting error for the same

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-06-26T06:05:57

The Oracle CONCAT function only takes two, not three or more, parameters. Instead of using CONCAT, just use the concatenation operator:

CREATE VIEW ITCC.release_testcase_count AS (
    SELECT rtm.requirement_id || '-' || tct.release_id AS id,
    ...
)

Or, if you really want to use CONCAT here, then you may chain them together:

CREATE VIEW ITCC.release_testcase_count AS (
    SELECT CONCAT(rtm.requirement_id, CONCAT('-', tct.release_id)) AS id,
    ...
)

Getting error ORA-00909: invalid number of arguments

1 Answers