Highlighted

Using COALESCE() in a query is breaking

LEGEND ,
Feb 08, 2016

Copy link to clipboard

Copied

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.

Views

582

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Using COALESCE() in a query is breaking

LEGEND ,
Feb 08, 2016

Copy link to clipboard

Copied

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.

Views

583

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 08, 2016 0
Most Valuable Participant ,
Feb 08, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 08, 2016 0
LEGEND ,
Feb 09, 2016

Copy link to clipboard

Copied

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

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0
LEGEND ,
Feb 09, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0
Most Valuable Participant ,
Feb 09, 2016

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0
LEGEND ,
Feb 09, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0
Most Valuable Participant ,
Feb 09, 2016

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0
LEGEND ,
Feb 09, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2016 0