Invalid number in Oracle SQL Case

Hi im habing trouble with a SQL case, the problem is im trying to run a case with 7 different columns, the columns can have different kinds of data (string,date,number) depending on an id.

This means that under some id's the rows in a column will be string while under other ids the rows in a column will be number.

I realise this isn't a conventional use of astructured database, but this specific table serve a specific purpose where this approach was deemed usefull in the past.

The case is supposed to only select a "then" when the column does have a number. However when i run it i get a invalid number ORA-01722. because one of the, rows will hold a string og date.

I realise its properly because oracle asses the sql before executing, and doesnt execute sequential, therefore giving errors on these column even though it wouldn actually have to calculate on the column under a given ID.

The code im trying to execute is the following, The hardcoded 1 and 2 before 'then' will change depending on ctrl_id (the unique id) and it will be the one securing that we only look and a list_val column/row with a number

WITH sampledata1 AS
(SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2
FROM dual),
sampledata2 AS
(SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2
FROM dual),
sampledata3 AS
(SELECT *
FROM sampledata1
UNION
SELECT *
FROM sampledata2)
SELECT CASE
WHEN ctrl_id = 1 THEN
AVG(list_val1)
over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
WHEN ctrl_id = 2 THEN
AVG(list_val2)
over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
END AS avg_val
FROM sampledata3 qd


Any suggestions to how i can make this work. either a workaround or a different approach ?

Thx in advance.

--------- Solution below

I used some of the suggestions and solutions posted below and got this code samble working. I will try and implement it with the system. Thx for the help everyone you saved me alot of headache.

WITH sampledata1
AS (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2 FROM DUAL),
sampledata2
AS (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2 FROM DUAL),
sampledata3
AS (SELECT * FROM sampledata1
UNION
SELECT * FROM sampledata2)
select ctrl_id,
avg(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null
then list_val1 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val1,
avg(CASE WHEN TRIM(TRANSLATE(list_val2, ' +-.0123456789', ' ')) is null
then list_val2 else null end) over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC) list_val2

from sampledata3 qd

de

AVG

VARCHAR

NUMBER

INTEGER

4 Answers
4

You can try to filtered out values with non-numeric symbols with something like

AVG(CASE WHEN TRIM(TRANSLATE(list_val1, ' +-.0123456789', ' ')) is null then list_val1 else null end) OVER (...)


NB! Unfortunately strings like '+12-.3' also will be recognized as a numeric and in this case you will get same ora-01722

The aggregate function like AVG does not work with VARCHAR data type, NUMBER or INTEGER is a must when such functions are being used.

AVG

VARCHAR

NUMBER

INTEGER

I have modified the query to have number instead of a string,

WITH sampledata1
AS (SELECT '1' ctrl_id, '23' list_val1, '43' list_val2 FROM DUAL),
sampledata2
AS (SELECT '2' ctrl_id, '34' list_val1, '45' list_val2 FROM DUAL),
sampledata3
AS (SELECT * FROM sampledata1
UNION
SELECT * FROM sampledata2)
SELECT CASE
WHEN ctrl_id = 1
THEN
AVG (list_val1)
OVER (PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
WHEN ctrl_id = 2
THEN
AVG (list_val2)
OVER (PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
END
AS avg_val
FROM sampledata3 qd


The output is

AVG_VAL
----------
23
45


Edit 1

Perhaps you can do something like the below to first ascertain row return NUMERIC or NON-NUMERIC.

Change as per your requirements.

WITH sampledata1
AS (SELECT '1' ctrl_id, '23' list_val1, 'Textfield' list_val2 FROM DUAL),
sampledata2
AS (SELECT '2' ctrl_id, 'Textfield' list_val1, '45' list_val2 FROM DUAL),
sampledata3
AS (SELECT * FROM sampledata1
UNION
SELECT * FROM sampledata2),
sampledata4
AS (SELECT LENGTH (TRIM (TRANSLATE (ctrl_id, ' +-.0123456789', ' ')))
ctrl_id,
LENGTH (TRIM (TRANSLATE (list_val1, ' +-.0123456789', ' ')))
list_val1,
LENGTH (TRIM (TRANSLATE (list_val2, ' +-.0123456789', ' ')))
list_val2
FROM sampledata3 qd -- group by ctrl_id
)
( SELECT CASE WHEN ctrl_id IS NULL THEN AVG (ctrl_id) ELSE 0 END ctrl_id,
CASE WHEN list_val1 IS NULL THEN AVG (list_val1) ELSE 0 END list_val1,
CASE WHEN list_val2 IS NULL THEN AVG (list_val2) ELSE 0 END list_val2
FROM sampledata4
GROUP BY ctrl_id, list_val1, list_val2)


Not sure why you are using that Analytic function. For your basic problem this would work:

SELECT AVG(CASE
WHEN ctrl_id = 1 THEN list_val1
WHEN ctrl_id = 2 THEN list_val2
END) AS avg_val
FROM sampledata3 qd


You're trying to get Average sum of list_val1, However list_val1 from sampledata2 query returns VARCHAR value.

list_val1

list_val1

sampledata2

You shouldn't use AVG on non-numeric values.

WITH sampledata1 AS
(SELECT '1' ctrl_id, '23' list_val1, '10' list_val2
FROM dual),
sampledata2 AS
(SELECT '2' ctrl_id, '45' list_vall, '90' list_val2
FROM dual),
sampledata3 AS
(SELECT *
FROM sampledata1
UNION
SELECT *
FROM sampledata2)
SELECT CASE
WHEN ctrl_id = 1 THEN
AVG(list_val1)
over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
WHEN ctrl_id = 2 THEN
AVG(list_val2)
over(PARTITION BY qd.ctrl_id ORDER BY qd.ctrl_id ASC)
END AS avg_val
FROM sampledata3 qd


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.