Add subquery in query - Oracle SQL

Question

My query return all days in month.

SELECT 
       EXTRACT( DAY FROM day ) ||' '|| substr(TO_CHAR( day, 'fmDAY' ),0,3) AS day,
       EXTRACT( DAY FROM day ) as day_id
FROM (
  WITH temp ( col ) AS (
    SELECT to_date(2, 'mm') --2 is February
    FROM   dual 
  )
  SELECT col + level - 1 AS day
  FROM   temp 
  CONNECT BY level <= last_day(col) - col + 1
  ORDER BY day
)

How get all days from query where DAY_ID not in ==> (Select day_id from table1)

Eg. table1 return 5,10,15

Query resault need to display all days except 5,10,15

MT0 · Accepted Answer · 2021-05-24 12:06:39Z

You can generate your calendar and then use NOT EXISTS:

WITH month ( col ) AS (
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 2 - 1)
  FROM   DUAL
),
calendar ( day, end_day ) AS (
  SELECT col, LAST_DAY(col)
  FROM month
UNION ALL
  SELECT day + INTERVAL '1' DAY, end_day
  FROM   calendar
  WHERE  day < end_day
)
SELECT EXTRACT( DAY FROM day ) ||' '|| TO_CHAR( day, 'fmDY' ) AS day,
       EXTRACT( DAY FROM day ) as day_id
FROM   calendar c
WHERE  NOT EXISTS (
  SELECT 1
  FROM   table1 t
  WHERE  c.day = t.day_id
)
ORDER BY c.day;

Which, for your sample data:

CREATE TABLE table1 ( day_id ) AS
SELECT DATE '2021-02-05' FROM DUAL UNION ALL
SELECT DATE '2021-02-10' FROM DUAL UNION ALL
SELECT DATE '2021-02-15' FROM DUAL;

Outputs:

DAY DAY_ID

1 MON 1

2 TUE 2

3 WED 3

4 THU 4

6 SAT 6

7 SUN 7

8 MON 8

9 TUE 9

11 THU 11

12 FRI 12

13 SAT 13

14 SUN 14

16 TUE 16

17 WED 17

18 THU 18

19 FRI 19

20 SAT 20

21 SUN 21

22 MON 22

23 TUE 23

24 WED 24

25 THU 25

26 FRI 26

27 SAT 27

28 SUN 28

db<>fiddle here

Gordon Linoff · Accepted Answer · 2021-05-24 12:27:35Z

1

One way to generate all rows in month for dates in another table is to use a recursive CTE:

create table table1 as
    select date '2021-04-16' as day_in from dual;

with cte (dte) as (
      select trunc(day_in, 'MON') as dte
      from table1
      union all
      select dte + interval '1' day
      from cte
      where dte < last_day(dte)
     )
select *
from cte;

Here is a db<>fiddle.

EDIT:

If you want the days not in a table, then use:

with cte (dte) as (
      select date '2021-02-01' as dte
      from dual
      union all
      select dte + interval '1' day
      from cte
      where dte < last_day(dte)
     )
select dte
from cte
where not exists (select 1 from table1 t1 where t1.day_id = cte.dte);

You can also use not exists using your query, but I find the recursive CTE easier to follow -- and it is standard SQL.

edited May 24, 2021 at 12:27

answered May 24, 2021 at 10:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Pointer Over a year ago

Tnx for help but your example is different and return different resault then my request.

James Over a year ago

Your request is far from clear, so it's completely normal that any answer doesn't fit your question

Gordon Linoff Over a year ago

@Pointer . . . Just use not exists or not in.

Collectives™ on Stack Overflow

Add subquery in query - Oracle SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

DAY	DAY_ID
1 MON	1
2 TUE	2
3 WED	3
4 THU	4
6 SAT	6
7 SUN	7
8 MON	8
9 TUE	9
11 THU	11
12 FRI	12
13 SAT	13
14 SUN	14
16 TUE	16
17 WED	17
18 THU	18
19 FRI	19
20 SAT	20
21 SUN	21
22 MON	22
23 TUE	23
24 WED	24
25 THU	25
26 FRI	26
27 SAT	27
28 SUN	28

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related