Problem with SUBSTR and GROUP BY
Hi people...
I'm needing a little help with this case. I'm executing this SQL and got error ORA-01722. I've tried to use number columns, alias and the syntax below and always got error. My problem is the SUBSTR, without it I can run the SQL with no error.
SQL> ED
Gravou arquivo afiedt.buf
1 SELECT TO_CHAR(DATA,'DD/MM') AS DATA,
2 SUBSTR(TO_CHAR(DATA,'DAY'),1,3) AS DIA_SEM,
3 SUM(DECODE(TIPOBAIXA,1,1,0)) AS INST,
4 SUM(DECODE(TIPOBAIXA,1,0,1)) AS REM,
5 SUM(DECODE(TIPOBAIXA,1,1,0))-SUM(DECODE(TIPOBAIXA,1,0,1)) AS DIFF
6 FROM BAIXA
7 GROUP BY TO_CHAR(DATA,'DD/MM'), TO_CHAR(DATA,'DAY')
8* ORDER BY TO_CHAR(DATA,'DD/MM') DESC
SQL> /
GROUP BY TO_CHAR(DATA,'DD/MM'), TO_CHAR(DATA,'DAY')
*
ERRO na linha 7:
ORA-01722: invalid number
Somebody has an idea of how I can solve this?
Thanks,
Fabiano