Looking for this?
select (start_date+level-1) fin_dt
where finyr_code = 13
connect by level <= (end_date-start_date)+1
from finyr_mast_13 a
join inv_hdr b
on a.fin_dt = b.fnv_dt
where b.fnv_dt is null;
I am not very clear on what you want. May be you need to explain it better.
I think you want something like this:
WITH days_wanted AS
, start_date + 1 - LEVEL AS a_date
-- START WITH finyr_code IN (13) -- if wanted
CONNECT BY finyr_code = PRIOR finyr_code
AND LEVEL <= end_date + 1 - start_date
AND PRIOR SYS_GUID () IS NOT NULL
FROM days_wanted d
LEFT OUTER JOIN inv_hdr i PARTITION BY (i.finyr_code)
ON i.inv_dt = d.a_date
WHERE i.inv_dt IS NULL
ORDER BY d.finyr_code
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data. For example, do any of the columns in inv_hdr (other than inv_dt) play any role in this problem, particularly finyr_code? If not, don't post them.
Will you ever want to do this query for 2 (or more) values of finyr_mst.finyr_code at the same time? Make sure your sample data and results show what you want.
Always say which version of Oracle you're using (e.g., 22.214.171.124.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
i got right answer.thanks for support..romba nandri
select a.start_date+level-1 no_sale_date from
(select start_date start_date from finyr_mst where finyr_code=13) a
connect by level<=(select end_date-start_date from finyr_mst where finyr_code=13)+1
select inv_dt from inv_hdr where finyr_code=13