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:
| BATCH_JOB_ID | DONE |
|---|---|
| 1 | 1 |
| 2 | 1 |
7
You can include BATCH_JOB_ID in the PARTITION BY clause of the analytic function in the inner-most sub-query and then move the filter to the middle sub-query. You have not explained what the logic is that you want to implement but you probably do not want to LEFT OUTER JOIN and either want INNER JOIN or EXISTS:
select id,
1 AS done
from SP_BATCH_JOB bj
WHERE EXISTS(
select 1
from (
select min(c.id) over (partition by c.BATCH_JOB_ID, 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
and a.BATCH_JOB_ID = bj.id
)
Which, for the sample data, outputs:
| ID | DONE |
|---|---|
| 1 | 1 |
| 2 | 1 |
or:
select bj.id,
COALESCE(ok.done, 0) AS done
from SP_BATCH_JOB bj
LEFT OUTER JOIN (
select batch_job_id,
COUNT(*) AS done
from (
select min(id) over (partition by BATCH_JOB_ID, REQ_ID) min_id,
REQ_ID,
id,
STATUS_ID,
BATCH_JOB_ID
from pinv_cmd
)
where id = min_id
and STATUS_ID = 3
GROUP BY batch_job_id
) ok
ON (ok.BATCH_JOB_ID = bj.id)
Which, if you use UNION ALL (instead of UNION: for your sample data:
create table SP_BATCH_JOB(id, name) AS
select 1, 'First job' from dual union all
select 2, 'Second job' from dual union all
select 3, 'Third job' from dual;
create table PINV_CMD (id, status_id, req_id, batch_job_id) AS
select 1, 3, 55, 1 from dual union all
select 2, 5, 55, 1 from dual union all
select 3, 3, 58, 2 from dual union all
select 4, 3, 58, 2 from dual union all
select 3, 3, 58, 2 from dual union all
select 4, 3, 58, 2 from dual;
Outputs:
| ID | DONE |
|---|---|
| 2 | 2 |
| 1 | 1 |
| 3 | 0 |
fiddle
3
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
| ID | NAME | DONE |
|---|---|---|
| 1 | First job | 2 |
| 2 | Second job | 2 |
| 3 | Third job | null |
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
| ID | NAME | DONE |
|---|---|---|
| 1 | First job | 1 |
| 2 | Second job | 1 |
| 3 | Third job | null |
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;
| ID | NAME | DONE |
|---|---|---|
| 1 | First job | 1 |
| 2 | Second job | 1 |
| 3 | Third job | 0 |
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.
4