7 Replies Latest reply on Feb 9, 2016 11:33 AM by WolfShade

    Using COALESCE() in a query is breaking

    WolfShade Level 4

      Hello, all,

       

      I've got a query that (pseudocode) is like:

       

      SELECT fname, lname, ORGS.EntityName dir, Table2b.EntityName div, Table3.personalid, COALESCE(Table2b.EntityName, ORGS.EntityName, 'N/A') org
      FROM personnel, (ORGS INNER JOIN ORGS Table2b ON ORGS.OrgID = Table2b.ParentID), Table3
      
      

       

      I'm getting an error message:  "Table2b"."EntityName" : invalid identifier.  I've also tried using the alias (dir,div), same error message.

       

      ORGS table has organizations; some organizations are "parent" organizations of other organizations, and has an ORGID and PARENTID.

       

      How can I use COALESCE() (if one is blank/null, get the other; if that's blank/null, use 'N/A') in this query?

       

      V/r,

       

      ^_^

       

      UPDATE:  I should probably mention that this is an Oracle database.  10g, I think.

        • 1. Re: Using COALESCE() in a query is breaking
          Carl Von Stetten Adobe Community Professional & MVP

          Does Oracle automatically join all tables in the FROM clause (except when you join to a second instance of the same table via alias, as you have)?

          • 2. Re: Using COALESCE() in a query is breaking
            WolfShade Level 4

            I can't say, for sure, but I believe it does.

             

            V/r,

             

            ^_^

            • 3. Re: Using COALESCE() in a query is breaking
              WolfShade Level 4

              I just learned from a SO thread that Oracle versions prior to 12c only supports one level down (child-level) subquery referencing.  If you need more than that (grandchild-level or greater), you have to upgrade to at least Oracle 12c.

               

              I thought that was it - it isn't.  What I'm doing IS only child-level.  Back to square one.

               

              V/r,

               

              ^_^

              • 4. Re: Using COALESCE() in a query is breaking
                Carl Von Stetten Adobe Community Professional & MVP

                There are two places in your SELECT statement that reference Table2b.EntityName (once when you include it with an alias as "div" and again in the COALESCE function).  Which is the error referring to?

                 

                Oh wait!  You don't give an alias to your subquery.  The table identifiers inside the subquery don't make it out of the subquery, so Table2b doesn't exist as far as Oracle is concerned.  Try giving the subquery the alias Table2b (or something else) and adjust the SELECT statement references to subquery columns accordingly.

                 

                SELECT fname, lname, ORGS.EntityName dir, Table2b.EntityName div, Table3.personalid, COALESCE(Table2b.EntityName, ORGS.EntityName, 'N/A') org  
                FROM personnel, (ORGS INNER JOIN ORGS Table2b ON ORGS.OrgID = Table2b.ParentID) Table2b, Table3
                
                • 5. Re: Using COALESCE() in a query is breaking
                  WolfShade Level 4

                  So, if the aliases (aliasii?) were outside of the subquery, the subquery could utilize them, but not the other way 'round??

                   

                  I'll give your suggestion a try, and report back.  Thanks!

                   

                  V/r,

                   

                  ^_^

                  • 6. Re: Using COALESCE() in a query is breaking
                    Carl Von Stetten Adobe Community Professional & MVP

                    No, the subquery itself must have an alias, as any aliases or table identifiers used inside the subquery are stripped off as the result set exits the subquery.  Maybe my example was confusing.  It would be more clear if you did this:

                     

                    SELECT fname, lname, ORGS.EntityName dir, Child.EntityName div, Table3.personalid, COALESCE(Child.EntityName, ORGS.EntityName, 'N/A') org    
                    FROM personnel, (ORGS INNER JOIN ORGS Table2b ON ORGS.OrgID = Table2b.ParentID) Child, Table3
                    

                     

                    Actually, the more I think on it, that part of the FROM statement doesn't even look like a subquery.  A subquery should have a SELECT...FROM... syntax.  What you have just looks like a convoluted JOIN.  Here is how I visualize it (if I were writing the query as T-SQL for SQL Server ):

                     

                    SELECT personnel.fname, personnel.lname, ORGS.EntityName AS dir, ORGS_DIV.EntityName AS div, Table3.personalid, COALESCE(ORGS_DIV.EntityName, ORGS.EntityName, 'N/A') AS org
                    FROM personnel 
                         INNER JOIN ORGS ON personnel.OrgID = ORGS.OrgID
                         INNER JOIN ORGS AS ORGS_Div ON ORGS.OrgID = ORGS_Div.ParentID
                         INNER JOIN Table3 ON ..... {not sure what the common key is here}
                    

                     

                    I have no experience with Oracle, so maybe if you define table relationships elsewhere it automatically knows how to join those tables together without having to put in all the ON clauses.  Or do you have a WHERE clause that defines the associated columns?

                    • 7. Re: Using COALESCE() in a query is breaking
                      WolfShade Level 4

                      I normally use LEFT INNER JOINs when writing something like this; but this was handed to me (legacy from a developer who isn't here, anymore), and I didn't feel like messing with it, since it worked (before I was tasked with making a minor modification.)

                       

                      The odd thing is that the query works without the COALESCE() command, so the FROM subset (you're right, it isn't a subquery) works, just not for COALESCE(), for some reason.  But I'll give your second suggestion a shot to see if that works.

                       

                      Thanks!

                       

                      V/r,

                       

                      ^_^