CODEWITHSUNDEEP
sqloracle-databaseleft-join
yesco1
yesco1

Reputation: 391

Retrieving a column from a left join subquery in Oracle

I am getting an error [Error] Execution (12: 25): ORA-00904: "N"."PORT_AID": invalid identifier after I added a left outer join EDW_SPLITTER_NT_PORT to my spl sub query. How can i get my query to work? It is getting the N.PORT_AID from the n subquery and its not reaching it in the spl? Should i change my left join EDW_SPLITTER_NT_PORT in the spl subquery and join it instead to the n subquery at the bottom.

EDW_SPLITTER AND EDW_SPLITTER_NT_PORT are related to each other so thats why I added the left join to that table. Any suggestions?

Select spl.splitter_addr AS splitter_address,
          spl.splitter_port,
          spl_port_status
          from (
   SELECT splitter.splitter_addr AS splitter_address,
          splitter.splitter_port,
          CASE
             WHEN splitter.SPLITTER_PORT_STATUS = 'ACTIVE'
             THEN
                CASE
                   WHEN n.PORT_AID IS NULL THEN 'INACTIVE'
                   ELSE 'ACTIVE'
                END
             ELSE --follow through with whatever splitter_port_status other than active remains:
                DECODE (splitter.splitter_port_status,
                        'IN-ACTIVE', 'INACTIVE',
                        splitter.splitter_port_status)
          END
             AS splitter_port_status

     FROM [email protected] splitter 
                          left outer join EDW_SPLITTER_NT_PORT nt
    --  ON     splitter.splitter_addr = nt.splitter_addr
    ON     splitter.eid = nt.eid
                          AND splitter.rack = nt.rack
                          AND splitter.shelf = nt.shelf
                          AND splitter.card = nt.card
                          AND splitter.port = nt.port)  spl


          LEFT OUTER JOIN
          (SELECT a.eid,
                  a.location_id,
                  o.rack,
                  o.shelf,
                  TO_NUMBER (SUBSTR (card, -2, 2)) AS slot,
                  o.port,
                  o.ont,
                  o.port_aid,
                  o.ont_type
             FROM [email protected] a
                  LEFT OUTER JOIN [email protected] o
                     ON a.eid = o.eid
           UNION ALL
           SELECT b.eid,
                  b.location_id,
                  1 rack,
                  1 shelf,
                  bo.card AS slot,
                  bo.port,
                  bo.ont,
                  REPLACE (bo.ont_aid, 'ONT', 'ONT-1-1') AS port_aid,
                  bo.ont_type
             FROM [email protected] b
                  LEFT OUTER JOIN [email protected] bo
                     ON b.eid = bo.eid) n
             ON     spl.eid = n.eid
                AND n.rack = spl.rack
                AND n.shelf = spl.shelf
                AND n.slot = spl.slot
                AND n.port = spl.port
                AND n.ont = spl.ont

Upvotes: 0

Views: 506

Answers (2)

Boneist
Boneist

Reputation: 23588

If I read your question correctly, you're saying that the PORT_AID column comes from the n subquery. However, you're trying to use it inside the spl subquery, which is out of scope for the n subquery. You'd have to add further joins in the spl subquery in order to expose that column in the subquery, or you could leave it and reference it in the outer query.

Also, the splitter alias you're using in the outer select isn't valid.

Upvotes: 1

JNevill
JNevill

Reputation: 50200

Your subquery spl:

SELECT splitter.splitter_addr AS splitter_address,
    splitter.splitter_port,
    CASE 
        WHEN splitter.SPLITTER_PORT_STATUS = 'ACTIVE'
            THEN CASE 
                    WHEN n.PORT_AID IS NULL
                        THEN 'INACTIVE'
                    ELSE 'ACTIVE'
                    END
        ELSE --follow through with whatever splitter_port_status other than active remains:
            DECODE(splitter.splitter_port_status, 'IN-ACTIVE', 'INACTIVE', splitter.splitter_port_status)
        END AS splitter_port_status
FROM [email protected] splitter
LEFT JOIN EDW_SPLITTER_NT_PORT nt
    --  ON     splitter.splitter_addr = nt.splitter_addr
    ON splitter.eid = nt.eid
        AND splitter.rack = nt.rack
        AND splitter.shelf = nt.shelf
        AND splitter.card = nt.card
        AND splitter.port = nt.port

makes reference to n.PORT_AID but there is no derived table n in this subquery. It has no idea what you are talking about when you say n.PORT_AID here.

Upvotes: 0

Related Questions