ORA-01722 error but column is numeric

[cantor001]

Help, I've been using Oracle for 3 weeks and can't get a simple query to work. Googled it, asked the local database guru, looked at Oracle ref books, searched forum threads, gave up

Its only when I use a column from the joined table that the query fails - never seen anything like it.

Environment is: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

a) This query
select ' ' as analyte_code, 0 as as_analysed from dual union all
select wos.analyte_code, wos.reportable_result_num
from REPORTING.vw_work_order_sample_result wos
join REPORTING.vw_chn_analysis_analytes chn
on chn.analyte_code = wos.analyte_code
where wos.work_order_code = 'MyWorkOrder'
and wos.analyte_code = 'N (ad)'
and chn.analyte_desc = 'Nitrogen';

yields:

0
N (ad) 1.03

which works. Yay.

b)
take out the base table analyte code test...

select ' ' as analyte_code,0 as as_analysed from dual union all
select
wos.analyte_code,
wos.reportable_result_num
from REPORTING.vw_work_order_sample_result wos
join REPORTING.vw_chn_analysis_analytes chn
on chn.analyte_code = wos.analyte_code
where wos.work_order_code = 'MyWorkOrder'
and chn.analyte_desc = 'Nitrogen';

and it fails with a ORA-01722 error.

Any idea what is going on ?

thanks
can

Defs:

1)
create or replace force view REPORTING.vw_chn_analysis_analytes as
select 'C (ad)' as analyte_code, 'Carbon' as analyte_desc from dual union all
select 'H (ad)', 'Hydrogen' from dual union all
select 'Huc (ad)', 'Hydrogen' from dual union all
select 'N (ad)', 'Nitrogen' from dual;

2) REPORTING.vw_work_order_sample_result - returns a result set and confirmed that column 'reportable_result_num'
is a number by issuing things like this and making sure a result set is returned...

select wos.reportable_result_num from REPORTING.vw_work_order_sample_result wos where
wos.work_order_code = 'MyWorkOrder' and wos.analyte_code = 'N (ad)'
union all
select 0 from dual

 

[SantaMufasa]

Cantor,

We can help you better if you could please post CREATE TABLE... and some INSERT INTO... statements for the tables involved in your failing query(ies). It would be helpful, as well, if you can run the CREATE..., INSERT and SELECT statements as an Oracle user in your environment to ensure that the failure still occurs in the separate user before you post the statements here.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[cantor001]

Solved it. What a performance. The view 'REPORTING.vw_work_order_sample_result' had a number of dependent views, one of which had an ORDER BY clause put in while I was testing it's output. Obviously a view with an order by is a no-no (I vaguely remember being a bit surprised at the time that it allowed the view to actually compile), once it had been removed the issue disappeared. Yay.