Search code examples
sqloracle-databasenestedsubstringidentifier

Trouble with nested SELECT statements with mutiple substrings, Oracle

This is in Oracle. I'm having an issue that I'm not sure can actually be solved. But I've created a query that essentially compares several different fields and needs to be able to utilize 2 substring columns and compare them to each other. I'm not overly sure how to go about doing this as I've tried the following code blocks and I keep getting either an "invalid identifier" error or "single-row subquery returns more than one row." I know why the latter comes up and thats not the issue. I'm trying to figure out why my substring column's are unable to be compared against each other.

Code samples:

This one gives me the correct columns so I know its not the syntax.

select substr(p.new_plan, 1, 1) as np, substr(p.old_plan, 1, 1) as op 
from sbsb_plan_conv p, cmc_sbel_elig_ent e
where p.sbsb_ck = e.sbsb_ck

This is the full code block which gives me the "OP": invalid identifier error

SELECT p.cspi_id, p.sbel_eff_dt, co.new_plan, co.ch_dt, co.sbsb_ck
FROM sbsb_plan_conv co, cmc_sbel_elig_ent p
WHERE co.ch_dt > p.SBEL_EFF_DT
AND co.new_plan <> p.CSPI_ID
AND co.sbsb_ck = p.sbsb_ck
AND p.cspi_id   = co.OLD_PLAN
and (p.cspd_cat = (
          select substr(o.old_plan, 1, 1) as op  
          from sbsb_plan_conv o, cmc_sbel_elig_ent e
          where op = (
                select substr(q.new_plan, 1, 1) as np   
                from sbsb_plan_conv q, cmc_sbel_elig_ent w
                where q.sbsb_ck = w.sbsb_ck)
          and o.sbsb_ck = e.sbsb_ck)
or p.cspd_cat = (
          select substr(o.new_plan, 1, 1) as np   
          from sbsb_plan_conv o, cmc_sbel_elig_ent e
          where np = (
                select substr(q.old_plan, 1, 1) as op  
                from sbsb_plan_conv q, cmc_sbel_elig_ent w
                where q.sbsb_ck = w.sbsb_ck)
          and o.sbsb_ck = e.sbsb_ck)
            )
AND (p.SBEL_ELIG_TYPE = 'tm'
OR p.sbel_elig_type = 'ce'
OR p.SBEL_ELIG_TYPE = 'TM'
OR p.sbel_elig_type = 'CE')

I had originally tried to use the two substring names as a conditional but that didn't work either. What am I doing wrong here?

Solution

  • You can't use aliases that were defined in the select clause in the where clause of the same query.

    Change this:

    select substr(o.old_plan, 1, 1) as op  
from sbsb_plan_conv o, cmc_sbel_elig_ent e
where op = ...

    To this:

    select substr(o.old_plan, 1, 1) as op  
from sbsb_plan_conv o, cmc_sbel_elig_ent e
where substr(o.old_plan, 1, 1) = ...

    Related