Databases
StackOverflow2013
Postsdbo
POOracle SQL(Plus) ORDER BY - Invalid Identifier (Nested Subquery)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle SQL(Plus) ORDER BY - Invalid Identifier (Nested Subquery)
    text
    copied!<p>First off - already looked at a similar question <a href="https://stackoverflow.com/questions/7732019/invalid-identifier-error-on-field-created-with-select-statement">here</a>, but it did not exactly pertain to my problem.</p> <p>Secondly, this is a homework assignment, but the question pertains to SQL syntax - I already wrote the query and the results are as expected, just unordered.</p> <p>Thirdly, I believe I know why the error is occurring - the sub-quer(y)(ies) are affecting the ordering of the outer query (maybe), and thus an order cannot be created based on the way that the queries are nested.</p> <p>Here is the query:</p> <pre><code>SELECT DISTINCT E.ssn, E.lname FROM employee E, works_on W WHERE E.ssn = W.essn AND W.pno NOT IN (SELECT P.pnumber FROM project P WHERE P.plocation = 'Houston') MINUS (SELECT E.ssn, E.lname FROM employee E, works_on W, project P WHERE E.ssn = W.essn AND W.pno = P.pnumber AND P.plocation = 'Houston') ORDER BY E.lname; </code></pre> <hr> <pre><code>SSN LNAME --------- --------------- 123456789 Smith 453453453 English 987987987 Jabbar 999887777 Zelaya </code></pre> <p>Now if I remove <code>ORDER BY E.lname</code> from the end of the query, it runs fine. Otherwise, I get an "invalid identifier" error. I assume this is because <code>E.lname</code> means nothing at that point in the query.</p> <p>So my question is... how can I "wrap" the queries in such a way (as referenced <a href="https://stackoverflow.com/questions/7732019/invalid-identifier-error-on-field-created-with-select-statement">here</a>) that I can sort the results by <code>E.lname</code>?</p> <p>I've already tried wrapping it in what feels like over 9,000 ways, but I haven't found a solution that works. I'm also new to SQL, so I doubt that I could change the structure of the query to give me the same results using any amount of sub-queries less than what I currently have.</p> <p>Thank you to anyone who takes the time to help me out, much appreciated! If it is necessary to provide the table creation code, let me know!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload