Ora-00904: Invalid Identifier In This Instance

Here is my solution, I want to display all the information in 3 tables with salary, when I put salary at then end, no matter what I tried,I still got wrong. Someone help me fix. I

Solution 1:

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB, '0'AS S.SALARY
    from HW1_PERSON M
    innerjoin HW1_BUILDING Bld
    ON M.id = Bld.id
    INNERJOIN HW1_PERSON M 
    ON Bld.id = M.id
    INNERJOIN HW1_PERSON M 
    ON M.id = Bld.id
    InnerJOIN HW1_BUILDING Bld
    ON Bld.id = M.id
    INNERJOIN HW1_BUILDING C
    ON M.id = C.id
    INNERJOIN HW1_PERSON D
    ON M.id = D.id
    UNIONALLSELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB,S.SALARY FROM HW1_STAFF S
    where S.SALARY =NULL
    ;

I Your First Query Column is Not Exist S.SALARY so set Default is '0' OR ''

Solution 2:

Firstly, you have made some unnecessary joins here.

You wrote

from HW1_PERSON M
innerjoin HW1_BUILDING Bld
ON M.id = Bld.id
INNERJOIN HW1_PERSON M 
ON Bld.id = M.id
INNERJOIN HW1_PERSON M 
ON M.id = Bld.id
InnerJOIN HW1_BUILDING Bld
ON Bld.id = M.id
INNERJOIN HW1_BUILDING C
ON M.id = C.id
INNERJOIN HW1_PERSON D
ON M.id = D.id

but, if you had written this, then it would be enough

from HW1_PERSON M
inner join HW1_BUILDING Bld
ON M.id = Bld.id
INNER JOIN HW1_BUILDING C
ON M.id = C.id

Moreover, you have used both M and D as aliases of the same table HW1_PERSON - which causes error while executing the query

Secondly, as mentioned in comments, your first query doesn't contain S.SALARY column from HW1_STAFF. From your table structures, I think you can get the column by doing this in your first query.

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY
from HW1_PERSON M
innerjoin HW1_BUILDING Bld
ON M.id = Bld.id
INNERJOIN HW1_BUILDING C
ON M.id = C.id
INNERJOIN HW1_STAFF S
ON S.PERSON_ID = M.id

Moreover, in your second query, you searched for NULL in S.SALARY, but by definition of your HW1_STAFF table, that column will not be null. So, in the second query, you will not get any result. Perhaps you should change that query - to something like this

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,D.DOB,S.SALARY FROM HW1_STAFF S
where S.END_DATE =NULL

Then, the whole query will look something like this

SELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY
from HW1_PERSON M
innerjoin HW1_BUILDING Bld
ON M.id = Bld.id
INNERJOIN HW1_BUILDING C
ON M.id = C.id
INNERJOIN HW1_STAFF S
ON S.PERSON_ID = M.id
UNIONALLSELECT Bld.id,C.code,M.FIRST_NAME,M.LAST_NAME,Bld.Address,M.ADDRESS,M.DOB,S.SALARY FROM HW1_STAFF S
where S.END_DATE =NULL
;