Using condition inside subquery

I need to extract only min(id) values. But I don't know how to use condition from upper query into inner query. The error is: invalid identifier. This is part of larger view, with many more tables and columns, for simplicity I've extracted only this part (I can't make CTE or something like that, because this is larger view). If I skip condition inside inner query, then it lasts forever. Is there some solution to this?

The info that I need is if result with min(id) is successfull (status_id = 3). In that case, count(*) = 1. So, result with min(id) was success.

select bj.*
    from SP_BATCH_JOB bj

        left join (
                 select a.BATCH_JOB_ID, count(*) over () done
                     from (select min(c.id) over (partition by c.REQ_ID) min_id,
                                  c.REQ_ID, c.id, c.STATUS_ID, c.BATCH_JOB_ID
                           from pinv_cmd c
                            where c.BATCH_JOB_ID = bj.id --error: invalid identifier
                           ) a
                     where a.id = a.min_id and a.STATUS_ID = 3) ok on ok.BATCH_JOB_ID = bj.id

Sample tables with expected result:

create table NITES_SP.SP_BATCH_JOB
(
    ID                 NUMBER       not null,
    NAME               VARCHAR2(50) not null
);

create table NITES_SP.PINV_CMD
(
    ID                  NUMBER                            not null,
    STATUS_ID           NUMBER                            not null,
    REQ_ID              NUMBER                            not null,
    BATCH_JOB_ID        NUMBER                            not null
);

insert into sp_batch_job (id, name)
select 1, 'First job' from dual
union
select 2, 'Second job' from dual
union
select 3, 'Third job' from dual
/

insert into pinv_cmd (id, status_id, req_id, batch_job_id)
select 1, 3, 55, 1 from dual
union
select 2, 5, 55, 1 from dual
union
select 3, 3, 58, 2 from dual
union
select 4, 3, 58, 2 from dual
union
select 5, 5, 58, 2 from dual
/

Expected result:

The really simple answer is to just remove the line that's erroring, as you're joining on that value later anyway:

select bj.*, ok.done
    from SP_BATCH_JOB bj

        left join (
                 select a.BATCH_JOB_ID, count(*) over () done
                     from (select min(c.id) over (partition by c.REQ_ID) min_id,
                                  c.REQ_ID, c.id, c.STATUS_ID, c.BATCH_JOB_ID
                           from pinv_cmd c
--                            where c.BATCH_JOB_ID = bj.id --error: invalid identifier
                           ) a
                     where a.id = a.min_id and a.STATUS_ID = 3) ok on ok.BATCH_JOB_ID = bj.id

But that gets 2 not 1, because you've added a window to the count(*) - it looks like that should not be there...

select bj.*, ok.done
    from SP_BATCH_JOB bj

        left join (
                 select a.BATCH_JOB_ID, count(*) done
                     from (select min(c.id) over (partition by c.REQ_ID) min_id,
                                  c.REQ_ID, c.id, c.STATUS_ID, c.BATCH_JOB_ID
                           from pinv_cmd c
                           ) a
                     where a.id = a.min_id and a.STATUS_ID = 3
                  group by a.BATCH_JOB_ID) ok on ok.BATCH_JOB_ID = bj.id

I've assumed that each req_id is linked to a single batch_job_id (so a batch has multiple requests), but if that isn't the case you can add that to the partition, as @MT0 pointed out:

                 select a.BATCH_JOB_ID, count(*) done
                     from (select min(c.id) over (partition by c.REQ_ID, c.BATCH_JOB_ID) min_id,

With your sample data the result is the same, and it wouldn't hurt to do that anyway even if they are linked.

---

Another way to get that result, and also show zero for batch 3 (which you may or may not want, as you haven't shown that at all in your example output) is to get the minimum status for each batch/request in a subquery, then count those:

select bj.id, bj.name,
  count(case when tmp.status_id = 3 then tmp.req_id end) as done
from sp_batch_job bj
left join (
  select c.batch_job_id, c.req_id,
    min(c.status_id) keep (dense_rank first order by c.id) as status_id
  from pinv_cmd c
  group by c.batch_job_id, c.req_id
) tmp
on tmp.batch_job_id = bj.id
group by bj.id, bj.name;

fiddle

(The 11gR2 version of db<>fiddle isn't currently working but I think that syntax is all supported...)

If you don't want to see the row for batch 3, either as null or zero, then use an inner join instead of an outer join.