CODEWITHSUNDEEP
sqloracle-database
davidlimpo
davidlimpo

Reputation: 73

How can I perform double to_char?

When I try this query:

SELECT EQUIPA,COUNT(*) TOTAL, to_char(DATAD, 'YY.MM.DD') Data FROM
  (
        SELECT EQUIPA, to_char(OS_DATACONCLUSAO,'YY.MM.DD HH24') DATAD

        FROM OS_CRM_DB_FINAL

        WHERE USERFECHO IS NOT null AND ( EQUIPA = 'AAA') AND (to_char(OS_DATACONCLUSAO,'YY.MM.DD HH24') >= '16.11.03 17:07') AND (to_char(OS_DATACONCLUSAO,'YY.MM.DD HH24') <= '16.11.15 17:07')

        ORDER BY DATAD DESC
  ) GROUP BY EQUIPA, Data
;

I got this error:

  1. 00000 - "invalid number"

What am I doing wrong? Thanks

Upvotes: 0

Views: 240

Answers (2)

Alex Poole
Alex Poole

Reputation: 191560

You're trying to group by data, which is a column alias defined in the same level of query, so that will generate ORA-00904: "DATA": invalid identifier.

You're converting a date to a string (with two digit years for some reason) and comparing with another string, which is not a good idea anyway - if the date column is indexed then converting it to a string prevents the index being used for the comparison, and it just generally makes more sense and is more efficient to compare the original data type. But the strings end up with different formats - one has minutes, the other doesn't.

Your inner query is converting the date to a string, and orders the results; that suggests that was an existing query that you're now trying to summarise. The order-by is pointless and won't affect the order of the results in the outer query.

If you really want to stick to a string in the inner query you can use a substring, as long as the date format is suitable, and use that substring in the group-by too:

SELECT EQUIPA, COUNT(*) TOTAL, substr(DATAD, 1, 8) Data FROM
  (
        ... -- original inner query
  ) GROUP BY EQUIPA, substr(DATAD, 1, 8)

or convert back to a date and then to a string again, and use the converted date in the group-by too, which is doing more work than you need but is logically what you originally tried to do:

SELECT EQUIPA, COUNT(*) TOTAL,
    to_char(to_date(DATAD, 'YY.MM.DD HH24'), 'YY.MM.DD') Data FROM
  (
    ... -- original inner query
  ) GROUP BY EQUIPA, to_date(DATAD, 'YY.MM.DD HH24')

or change the inner query to return the original date rather than a string; although the inner query isn't needed at all really:

SELECT EQUIPA, COUNT(*) TOTAL, to_char(TRUNC(OS_DATACONCLUSAO), 'YY.MM.DD') Data
FROM OS_CRM_DB_FINAL
WHERE USERFECHO IS NOT null
AND EQUIPA = 'AAA'
AND OS_DATACONCLUSAO >= TO_DATE('2016.11.03 17:07:00', 'YYYY.MM.DD HH24:MI:SS')
AND OS_DATACONCLUSAO <= TO_DATE('2016.11.15 17:07:00', 'YYYY.MM.DD HH24:MI:SS')
GROUP BY EQUIPA, TRUNC(OS_DATACONCLUSAO)
ORDER BY EQUIPA, TRUNC(OS_DATACONCLUSAO) DESC

I've changed the filters to compare the dates as dates rather than strings. The date is only converted to a string at the last moment for display. Also notice that I've ordered by the truncated date (which, by default, gives you the date with the time set to midnight). You could use the data column alias in the order-by (but nowhere else), and in this case it would be OK; but if you formatted the date in the result set differently e.g. MM/DD/YYYY you'd want to order by the date rather than the string.

Upvotes: 1

user5683823
user5683823

Reputation:

I didn't check the correctness of the inner query - you didn't provide test data and I am not going to create my own. But: If it is correct, all you need to do is to select OS_DATACONCLUSAO in the inner query ("subquery"), not its string representation. (You still use the string representation in the WHERE clause, just not in the SELECT.)

Try that and see if it works. Then we can discuss what you have in the WHERE clause; it's probably incorrect (dates shouldn't be compared as strings), and even if it is correct it's very inefficient. To compare properly, you should convert the strings you are comparing to into dates with to_date(), rather than convert your date values to string.

Upvotes: 1

Related Questions