Oracle invalid identifier error

Question

I get an oracle invalid identifier error for t.ev_no in the where clause of my inner query

First, let me explain my table structure and purpose of the query. I have a table named vendor which has following columns

1. ev_no
2. ev_name
3. ev_address1
4. ev_address2
5. ev_address3
6. ev_city
7. ev_state
8. ev_zip
9. payment_address
10. ev_mwbe

I am to display this information in a gridview, but the address is a single column which consist of address1, address2, address3, city, state and zip on each individual line. If any of these columns are NULL then I have to skip the line break. Hence the inner query with case which takes care of this.

The issue is somehow oracle is unable to recognize the t.ev_no in my where clause of the inner query. My query is as below

select t.ev_no,
       t.ev_type,
       t.ev_name,
       (select ADD1 || ADD2 || ADD3 || CITY || STATE || ZIP
          from (select CASE
                         WHEN ev_address1 is NULL then
                          ''
                         ELSE
                          ev_address1 || '<br>'
                       END AS "ADD1",
                       CASE
                         WHEN ev_address2 is NULL then
                          ''
                         ELSE
                          ev_address2 || '<br>'
                       END AS "ADD2",
                       CASE
                         WHEN ev_address3 is NULL then
                          ''
                         ELSE
                          ev_address3 || '<br>'
                       END AS "ADD3",
                       CASE
                         WHEN ev_city is NULL then
                          ''
                         ELSE
                          ev_city || '<br>'
                       END AS "CITY",
                       CASE
                         WHEN ev_state is NULL then
                          ''
                         ELSE
                          ev_state || '<br>'
                       END AS "STATE",
                       CASE
                         WHEN ev_zip is NULL then
                          ''
                         ELSE
                          ev_zip || '<br>'
                       END AS "ZIP"
                       from vendor where ev_no = t.ev_no)) as "ADDRESS",
       t.ev_state,
       t.ev_city,
       t.payment_address,
       t.ev_mwbe
  from vendor t
 where UPPER(ev_city) like :city
 order by t.ev_state, t.ev_city, t.ev_name

Please let me know how can I rectify the error.

Thanks

Answer 1

Don't remember exact problem but in oracle there is a limit to how far inside nested query you can use aliases. To fix this issue you can rewrite using WITH clause.

WITH addressquery 
     AS (SELECT ev_no, 
                CASE 
                  WHEN ev_address1 IS NULL THEN '' 
                  ELSE ev_address1 
                       || '<br>' 
                END AS "ADD1", 
                CASE 
                  WHEN ev_address2 IS NULL THEN '' 
                  ELSE ev_address2 
                       || '<br>' 
                END AS "ADD2", 
                CASE 
                  WHEN ev_address3 IS NULL THEN '' 
                  ELSE ev_address3 
                       || '<br>' 
                END AS "ADD3", 
                CASE 
                  WHEN ev_city IS NULL THEN '' 
                  ELSE ev_city 
                       || '<br>' 
                END AS "CITY", 
                CASE 
                  WHEN ev_state IS NULL THEN '' 
                  ELSE ev_state 
                       || '<br>' 
                END AS "STATE", 
                CASE 
                  WHEN ev_zip IS NULL THEN '' 
                  ELSE ev_zip 
                       || '<br>' 
                END AS "ZIP" 
         FROM   vendor) 
SELECT t.ev_no, 
       t.ev_type, 
       t.ev_name, 
       a.add1 || a.add2 || a.add3 || a.city || a.state || a.zip AS "ADDRESS", 
       t.ev_state, 
       t.ev_city, 
       t.payment_address, 
       t.ev_mwbe 
FROM   vendor t 
       join addressquery a 
         ON t.ev_no = a.ev_no 
WHERE  1 = 1 
       AND Upper(ev_city) LIKE :city 
ORDER  BY t.ev_state, 
          t.ev_city, 
          t.ev_name 

Answer 2

So I found that the nesting of queries was causing this issue. The below query works successfully though the readability of the query takes a major hit.

select t.ev_no,
       t.ev_type,
       t.ev_name,
       (select CASE
                 WHEN ev_address1 is NULL then
                  ''
                 ELSE
                  ev_address1 || '<br>'
               END || CASE
                 WHEN ev_address2 is NULL then
                  ''
                 ELSE
                  ev_address2 || '<br>'
               END || CASE
                 WHEN ev_address3 is NULL then
                  ''
                 ELSE
                  ev_address3 || '<br>'
               END || CASE
                 WHEN ev_city is NULL then
                  ''
                 ELSE
                  ev_city || '<br>'
               END || CASE
                 WHEN ev_state is NULL then
                  ''
                 ELSE
                  ev_state || '<br>'
               END || CASE
                 WHEN ev_zip is NULL then
                  ''
                 ELSE
                  ev_zip || '<br>'
               END
          from LMS_APPL.tlsev_external_vendor
         where ev_no = t.ev_no) as "ADDRESS",
       t.ev_state,
       t.ev_city,
       t.payment_address,
       t.ev_mwbe
  from vendor t
 where UPPER(ev_city) like :city
 order by t.ev_state, t.ev_city, t.ev_name