Using COALESCE() in a query is breaking

[WolfShade]

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.

[Carl Von Stetten]

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)?

[WolfShade]

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

V/r,

^_^

[WolfShade]

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,

^_^

[Carl Von Stetten]

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

[WolfShade]

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,

^_^

[Carl Von Stetten]

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?

[WolfShade]

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,

^_^