Hello all,

I got an issue how to make the following query is working:

================================================== =======

SELECT SchoolInfo.Year, (SELECT StatusInfo.StatusText FROM StatusInfo WHERE StatusInfo.StatusId = SchoolInfo.StatusId ) Status
FROM SchoolInfo
WHERE SchoolInfo.Year = P_YEAR AND Status = P_STATUS;

================================================== =======
I tried to run above query with real value for Year and Status but it display the error: "Status is invalid identifier". How do I can use ALIAS to filter for parameter. Thanks in advance.

Please publish the exact query which you "tried to run above query with real value for Year and Status".

Two obvious mistakes. The first one: space between "StatusInfo" and ".StatusText" in the second line. The second one: comma behind the "Status," (the second line again).

You should consider copy/pasting of your SQL*Plus session, as it helps a lot. Don't forget to FORMAT your code and enclose it into the [code] tags to preserve formatting.

I have corrected my typo. I added another filter for alias name: Status in order to filter the record by Year and Status. Originally, the query is working well if filter by Year. Here is the query with values:

SELECT SchoolInfo.Year, (SELECT StatusInfo.StatusText FROM StatusInfo WHERE StatusInfo.StatusId = SchoolInfo.StatusId ) Status
FROM SchoolInfo
WHERE SchoolInfo.Year = '2006' AND Status = 'Graduated';

The error occurred at Status is invalid identifier??? I am new with Oracle, so an idea how to change the code to make it happen. Thanks in advance.

Thanks for your detailed code. It works very well.