SQL query error ORA-01722 but working on oracle

Get Help
1

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

2

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.

3

Thanks Andrew
but it's still the same error.

The fact is that this field I've recently changed to number ("Quantitat") in administration panel

image
image1671×484 22.9 KB

but when editing questions, it apears like text...

image
image563×863 23.9 KB

it can be something about this?
it has to be something in metabase since this sentence in oracle is working fine.

thanks a lot,
Anna

4

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.

5

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

)
select TMAX
from Dades
where TMAX>ALARM_LIMIT

I'm lost :frowning:

6

That's returning the maximum of a varchar, not a number.
Should be max(to_number(temperatura))

7

SELECT TO_NUMBER(23) as ALARM_LIMIT, max(to_number(TEMPERATURA)) as TMAX
FROM METEOR_MAT_DADES5MIN

ORA-01722: número no vàlid

max(to_number(TEMPERATURA) comes an error

8

Are some of the values in TEMPERATURA non-numeric?
The error is saying that it can't convert a varchar to a number.

Does Oracle have a function to test for a numeric value?

1 Like
9

thanks, You gave me the light... I have 24.3 as a temperature. Oracle convert its but metabase not.
thanks a lot! I was crazy about that!!

10

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.

1 Like