I am trying to troubleshoot a Discoverer 4 error for a user where it involves a join of two folders. Oracle 11g, 11.1.0.7.0
When I retrieved the sql from Discoverer and trimmed it down
significantly, I am able to recreate the error when just running in Toad.
SELECT r.JOB_NAME
, t.WORK_ORDER
FROM
(SELECT JOB_NAME FROM APPS.WIP_ROUTINGS_3I_V where ASSY$ITEM = 'MAP32G' and JOB_NAME='1166661') r,
(SELECT WORK_ORDER FROM mfg_user.TPNonConform WHERE business_unit = 303 and work_order = '1166661') t
WHERE r.JOB_NAME = t.WORK_ORDER;
When I run it I get the following error which is the same
error coming from Discoverer.
ORA-01722: invalid number
ORA-02063: preceding line from TIPP.WORLD
Both columns in the join are varchar2.
If I swap out the t line with the below, same error, so the problem isn’t the mfg_user.TPNonConform view.
SELECT WORK_ORDER FROM nonconform@tipp.world WHERE business_unit = 303 and work_order = '1166661') t
If I swap out the t line with the below, no error.
SELECT '1166661' as WORK_ORDER from dual) t
If I swap out the r line with the below, and use the original t line, no error.
(SELECT WIP_ENTITY_NAME AS JOB_NAME FROM WIP.WIP_ENTITIES where WIP_ENTITY_NAME='1166661') r
Discoverer formatted the join in a strange way. When I write my own join like below against the two
views that Discoverer started with, it works fine. So I can’t really figure out where the problem is. But I
can’t control how Discoverer formats its SQL.
select a.JOB_NAME, a.ASSY$ITEM, b.ncnumber
from APPS.WIP_ROUTINGS_3I_V a, mfg_user.TPNonConform b
where b.WORK_ORDER = a.JOB_NAME
and a.ASSY$ITEM = 'MAP32G'
and b.work_order = '1166661';
Any ideas or suggestions. Thanks in advance.