Condition in WHERE clause (Oracle)

I need a query which returns data based on what month/year it is. Below is a subquery i wrote which returns one row – start_date and end_date are the values i need to use in my main query

WITH SUBQ AS (SELECT 
    dim.MONTH_NAME as current_month_name
   ,dim.year_period as current_month
   ,dim.PERIOD_YEAR as YEAR
   ,CASE WHEN dim.year_period NOT LIKE '%01' THEN to_number(CONCAT(to_char(dim.PERIOD_YEAR-1) , '01' ))
         WHEN dim.year_period LIKE '%01'THEN to_number(CONCAT(to_char(dim.PERIOD_YEAR-2) , '01' ))
         END AS START_DATE 
   ,CASE WHEN dim.year_period NOT LIKE '%01' THEN to_number(CONCAT(to_char(dim.PERIOD_YEAR) , '01'  ))
         WHEN dim.year_period LIKE '%01'THEN to_number(CONCAT(to_char(dim.PERIOD_YEAR-1) , '01'   )) END AS ENDDATE 
  from dim_periods dim    WHERE dim.year_period=to_number(to_char(sysdate, 'YYYYMM')))

Question is – how do i use values from subquery with one row in where clauses?
I need to get something like this, i just dont understand how should i join my subquery and the rest of the tables i use –

select * from financial_data fd 
where fd.year_period BETWEEN subq.start_date and subq.enddate


Source: sql

Leave a Reply