-1

I don't understand , why I am taking this error.


SELECT
    to_char(view_date, 'Month') MONAT ,
    COUNT(*) AS countx
FROM
    AXY_TABLE
GROUP BY
    to_char(view_date,'Month') 
ORDER BY
to_char(view_date, 'Month'),
    COUNT(*) desc;

When I execute this Query for a Interactive Report, it throws ORA-01722 Error. This Query run not only correctly in SQL developer but also as Classic Report correctly. When I changed the type to Interactive Report, throws it again the same error.

What should I do ?

Thanks a lot in advance.

dwh_asil
  • 5
  • 7
  • Does this answer your question? [SQL error "ORA-01722: invalid number"](https://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number) – padaleiana Jan 29 '21 at 12:35
  • 1
    I suspect a problem with `view_date`. The name suggests this is a `DATE` colum, but is it? Can you please check what data type that column is? – Thorsten Kettner Jan 29 '21 at 12:36
  • If VIEW_DATE's column datatype is DATE, then it works just fine on my Apex 18.2. Which Apex version do you use and - as Thorsten asked - is it really a DATE datatype column? – Littlefoot Jan 29 '21 at 12:38
  • Thanks a lot for your interest. My APEX Version is 20.2. View_Date in DB is DATE Data type, but I am changing it with to_char. Is this not enough ? In Addition to this I am using this script as SQL Query-Source for Interactive Report. When I change the type as Classic Report, then I don't have this error any more. – dwh_asil Jan 29 '21 at 13:01
  • Sorry, then I am out of clues. If `view_date` where a `VARCHAR2` column, then this would be very easy to explain. With it being a `DATE` column, however, the only conversion taking place is from date to string, which should be no problem at all. Nothing that would explain an ORA-01722 exception. – Thorsten Kettner Jan 29 '21 at 13:12
  • No problem. Thx. I have just ignored the Date, and executed Query only so : SELECT COUNT(*) AS countx FROM AXY_TABLE ORDER BY COUNT(*) AS countx; And it throws the same error. :-( – dwh_asil Jan 29 '21 at 13:22
  • The Problem is with COUNT(*) . It is too silly. – dwh_asil Jan 29 '21 at 13:25
  • When I use in a page 2 Interactive Report , I see this problem. At the second Report I take this error. First report runs correctly, but at the second Report fails. I don't know WHY ???? – dwh_asil Jan 29 '21 at 13:48
  • The query can't really be the problem. It must be your report. Are you doing something with your MONAT string in it apart from displaying it? – Thorsten Kettner Jan 29 '21 at 14:15
  • No, I have made nothing. – dwh_asil Jan 29 '21 at 14:56
  • Maybe this query isn't causing the error at all. Run the page in **debug mode** and then review debug info. It'll point you to exact error cause. – Littlefoot Jan 29 '21 at 15:50
  • Ok, I will do it. – dwh_asil Jan 29 '21 at 16:10

1 Answers1

0

Is the problem the ORDER BY clause? Try removing the aggregation from it:

SELECT
    to_char(view_date, 'Month') MONAT ,
    COUNT(*) AS countx
FROM
    AXY_TABLE
GROUP BY
    to_char(view_date,'Month') 
ORDER BY
to_char(view_date, 'Month'),
    2 desc;

This orders by the position of the second column of the projection. However, it is strictly unnecessary, as your result set will conatin only one row per MONTH, so you only need to sort by that.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I have removed completely ORDER BY Clause . But still it throws the same error. – dwh_asil Jan 29 '21 at 13:41
  • When I use in a page 2 Interactive Report , I see this problem. At the second Report I take this error. First report runs correctly, but at the second Report fails. I don't know WHY ???? – dwh_asil Jan 29 '21 at 13:47
  • Unfortunately it's rather difficult to debug a piece of code from a high level description of what it does. – APC Jan 29 '21 at 15:09