Hi,
I have this query that gets de max of TEMPERATURA and then it compares to 23 to get if the temperature is high or not.
This sql in oracle is working but the same code in a sql question from Metabase gets ORA-01722
Any help?
WITH MaxDia AS (
SELECT
TRUNC("DWSILVER"."METEOR_MAT_DADES5MIN"."DATA_LECTURA", 'dd') AS DATA_LECTURA,
max("TEMPERATURA") AS TMAX
FROM
"DWSILVER"."METEOR_MAT_DADES5MIN"
WHERE
"DWSILVER"."METEOR_MAT_DADES5MIN"."DATA_LECTURA" >= TRUNC(CURRENT_TIMESTAMP, 'dd')
AND "DWSILVER"."METEOR_MAT_DADES5MIN"."DATA_LECTURA" < TRUNC((CURRENT_TIMESTAMP + 1), 'dd')
GROUP BY
TRUNC("DWSILVER"."METEOR_MAT_DADES5MIN"."DATA_LECTURA", 'dd')
)
SELECT
DATA_LECTURA, TMAX,
CASE
WHEN TO_NUMBER(TMAX) > TO_NUMBER(23) THEN 'SUPERA 23'
ELSE 'SENSE ALERTA'
END AS "Alerta temperatura"
FROM MaxDia
ORDER BY DATA_LECTURA ASC
Shouldn't that read WHEN TO_NUMBER(TMAX) > TO_NUMBER('23') THEN 'SUPERA 23'
or just WHEN TO_NUMBER(TMAX) > 23 THEN 'SUPERA 23'
Looks like you're trying to convert a number to a number.
The Metadata section has no impact on a SQL question.
I'd start with a very simple query
SELECT TO_NUMBER(23)
then add your FROM clause with extra info. Find out when the error arrives.
Thanks again.
The error comes when you make the comparision.
This works:
SELECT TO_NUMBER(23) as ALARM_LIMIT, max(TEMPERATURA) as TMAX
FROM METEOR_MAT_DADES5MIN
This works:
With Dades as(
SELECT TO_NUMBER(23) as ALARM_LIMIT, max(TEMPERATURA) as TMAX
FROM METEOR_MAT_DADES5MIN
)
select TMAX
from Dades
this does'nt:
With Dades as(
SELECT TO_NUMBER(23) as ALARM_LIMIT, max(TEMPERATURA) as TMAX
FROM METEOR_MAT_DADES5MIN
this is one of the reasons that all my Metabase work is based on views rather than database tables. That conversion should be done in a view on the database, not in Metabase.