INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ORA-01722 error but column is numeric

ORA-01722 error but column is numeric

(OP)
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 smile

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

RE: ORA-01722 error but column is numeric

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,

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

RE: ORA-01722 error but column is numeric

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close