CODEWITHSUNDEEP
sqloracle-database
guru
guru

Reputation: 1

Error Msg = ORA-00909: invalid number of arguments

Error Msg = ORA-00909: invalid number of arguments

SELECT CASE 
         WHEN TO_DATE(TO_CHAR(TO_DATE(Aggregation.REPORTING_POSITION_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') > TO_DATE('2021-06-30 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 'FALSE' 
         ELSE 'TRUE' 
         WHEN TO_DATE(TO_CHAR(TO_DATE(Aggregation.REPORTING_POSITION_DATE,'MM/DD/YYYY HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') > TO_DATE('2021-06-30 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 'FALSE' 
         ELSE 'TRUE' 
       END 
FROM HKLRR_UAT_USER.aggr_707884

Upvotes: 0

Views: 130

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

If you really write queries like this, no wonder you get errors. That's unreadable.

Though, when properly formatted and errors fixed (it is WHEN that was misplaced), it seems to be returning something. To test it, I created a dummy CTE and removed Aggregation and HKLRR_UAT_USER as I don't have those users.

SQL> WITH
  2     aggr_707884
  3     AS
  4        (SELECT '2021-09-11 12:22:33' reporting_position_date FROM DUAL)
  5  SELECT CASE
  6            WHEN TO_DATE (
  7                    TO_CHAR (
  8                       TO_DATE (REPORTING_POSITION_DATE,
  9                                'YYYY-MM-DD HH24:MI:SS'),
 10                       'YYYY-MM-DD HH24:MI:SS'),
 11                    'YYYY-MM-DD HH24:MI:SS') >
 12                 TO_DATE ('2021-06-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 13            THEN
 14               'FALSE'
 15            WHEN TO_DATE (
 16                    TO_CHAR (
 17                       TO_DATE (REPORTING_POSITION_DATE,
 18                                'MM/DD/YYYY HH24:MI:SS'),
 19                       'YYYY-MM-DD HH24:MI:SS'),
 20                    'YYYY-MM-DD HH24:MI:SS') >
 21                 TO_DATE ('2021-06-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 22            THEN
 23               'FALSE'
 24            ELSE
 25               'TRUE'
 26         END result
 27    FROM aggr_707884
 28  /

RESUL
-----
FALSE

SQL>

I don't know why you nested that many TO_DATE/TO_CHAR functions; code could be shortened to

SQL> WITH
  2     aggr_707884
  3     AS
  4        (SELECT '2021-09-11 12:22:33' reporting_position_date FROM DUAL)
  5  SELECT CASE
  6            WHEN TO_DATE (REPORTING_POSITION_DATE, 'YYYY-MM-DD HH24:MI:SS') >
  7                 TO_DATE ('2021-06-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  8            THEN
  9               'FALSE'
 10            WHEN TO_DATE (REPORTING_POSITION_DATE, 'MM/DD/YYYY HH24:MI:SS') >
 11                 TO_DATE ('2021-06-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 12            THEN
 13               'FALSE'
 14            ELSE
 15               'TRUE'
 16         END result
 17    FROM aggr_707884
 18  /

RESUL
-----
FALSE

SQL>

Finally, as it seems you're repeating WHEN conditions (so remove one of them) and presuming that reporting_position_date column's datatype is DATE (it should be; don't store date values as strings), it becomes as simple as

SQL> WITH aggr_707884 AS (SELECT SYSDATE reporting_position_date FROM DUAL)
  2  SELECT CASE
  3            WHEN REPORTING_POSITION_DATE > DATE '2021-06-30' THEN 'FALSE'
  4            ELSE 'TRUE'
  5         END result
  6    FROM aggr_707884;

RESUL
-----
FALSE

SQL>

Upvotes: 2

Related Questions