Table or view does not exist - Oracle complains about a comma rather than an actual table or view name

Question

I've never seen this before... I have a query that starts off like this:

with q1 as
     (select a.V_ID, a.D_ID, a.C_ID,
               case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val,
               a.C_val
       from ab_a_table a
       where a.C_ID =  '00000003' -- '00000007' --  test values
             and a.B_VAL = '6010001'
             and a.Q = '11234567')
select case
           when ... /* rest of query omitted */

When I try to run this, Oracle complains about that a table or view does not exist. But it highlights the ',' on line 3, rather than an actual table/view name:

               case when a.percent > 0 THEN 'Y' ELSE 'N' end L_VAL,
                                                                  *
ERROR at line 3:
ORA-00942: table or view does not exist

The rest of the query I omitted is rather long and complex - I'll sanitize and post it if necessary - for now I'll just say that this error only started when I added a third subquery that referenced q1. In fact, it seems I can remove any one of the 3 subqueries and the whole thing will execute (though with incorrect results) so it feels like I've hit some kind of Oracle error rather than a pure SQL error. It's also interesting that I can run the body of q1 as a stand-alone query and it has no problems when I do that. Only when I run the entire query does it complain about the comma after the case in q1.

Has anyone ever experienced this?

(using Oracle 10g).

---

Edit: Tried added AS keyword. Results are now:

               case when a.perc_fault > 0 THEN 'Y' ELSE 'N' end AS L_VAL, a.C_VAL
                                                                     *
ERROR at line 3:
ORA-00942: table or view does not exist

It looks like the asterisk is in the same position, but under the V because the word L_VAL has been shifted by 3 characters. Very strange...

Answer 1

Assuming you are hitting the Oracle bug(s) and can't patch the database, you could try moving the subquery to a function. Not entirely sure this will work, and assumes your PL/SQL version is in a package, or there's one available that can have a function added:

In the package spec:

type q1_rec is record(
    d_id ab_a_table.v_id%TYPE,
    v_id ab_a_table.d_id%TYPE,
    c_id ab_a_table.c_id%TYPE,
    l_val char(1),
    c_val ab_a_table.c_val%TYPE);
type q1_arr is varray(9999); -- assuming you can pick a max size
function q1 return q1_arr pipelined;
pragma restrict_references(q1, wnds);

In the package body:

function q1 return q1_arr pipelined is
    cursor c is
        select a.V_ID, a.D_ID, a.C_ID,
               case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val,
               a.C_val
       from ab_a_table a
       where a.C_ID =  '00000003' -- '00000007' --  test values
             and a.B_VAL = '6010001'
             and a.Q = '11234567');
begin
    for r in c loop
        pipe row(r);
    end loop;
end;

And then in your main query replace the subquery with table(q1()).

Using a ref cursor or nested table might be a bit neater but would need a table type built outside the package, which I guess you want to avoid based on your extra-object comment about using a view.