invalid identifier with sub query

Question

i want to access st.Gr_number column in Subquery but it gives me error

ORA-00904: "ST"."GR_NUMBER": invalid identifier

    Select st.Gr_number,
st.STUDENT_ID, st.course, 
st.Student_Name,cr.course_name,
st.Status as Active,
st.GR_NUMBER as Fee_Detail,
(Select sum(nvl(r.amt, 0)-nvl(f.fee_amount, 0)) as Current_Balance
from FEE_SCHEDULE f 
full outer join (select gr_number, to_char(pmt_date,'yyyymm') pmt_month, sum(nvl(pmt_amount,0)) amt 
                 from FEE_PAYMENT_POSTED
                 Where pmt_type = 'FEE'
                 and   gr_number = st.Gr_number
                 group by gr_number, to_char(pmt_date,'yyyymm')
                ) r 
             on f.GR_NUMBER=r.GR_NUMBER and f.SCHEDULE_MONTH=r.pmt_month

where nvl(f.gr_number,r.gr_number) = st.Gr_number
) currentBalance

from student st inner join course cr on cr.course_id=st.course
where st.student_id=:P166_STUDENT_ID and upper(st.Status)='STUDYING';

Answer 1

In you inner query you are trying to use st.GR_NUMBER while it still hasn't been defined which it does in the outer query.

This is the place gr_number = st.Gr_number where the issue is.

Answer 2

This line is causing an error:

            and   gr_number = st.Gr_number

Although quite hard to tell from your formatting, this is nested two levels deep:

select . . . ,
       (select . . .
        from . . . 
             (select . . .
              from . . . gr . . .
              where . . . and
                    gr_number = st.Gr_number
             ) level3
       ) level2
from . . . st . . .

Oracle's scoping rules only allow table references to be recognized one level deep. So, within level3 you can reference level2, but not the outer level.

You have a very complicated query. There is no way to know if this is the only problem, so I am not going to even attempt fixing it.

I would suggest that you ask another question with a simplified query.