ORACLE Call function every range of weeks in query-CodePudding

I'm trying to use a query to call a function every range of 13 weeks (91 days) from the subscribe date of every users.

My user table look like this:

USER | SUBSCRIBE_DATE
  u1 |     2021-06-01
  u2 |     2021-06-14
...

I'am working on a query like this

SELECT
u.USER,
u.SUBSCRIBE_DATE as "Start date",
(
    SELECT
    u.SUBSCRIBE_DATE    ( level - 1 ) * 91 as "fin"
    from DUAL
    connect by level <= (
        (
            ( CURRENT_DATE - u.SUBSCRIBE_DATE )   91
        ) / 91
    )
) as "End date",
getfunction(u.user,"Start date","End date") as "Get function"
FROM User u

I would like to have a result like this:

USER |  Start date |    End date | Get function
  u1 |  2021-06-01 |  2021-08-30 |            X
  u1 |  2021-08-31 |  2021-11-29 |            X
  ...
  u2 |  2021-06-14 |  2021-09-12 |            X
  u2 |  2021-09-13 |  2021-12-12 |            X
  ...

Am i in a wrong direction?

Thank you

CodePudding user response：

The function should be called outside of the inner query, as the columns are not present in that moment. They don't exist in the inner query.

You should not use this column notation with blank spaces, just an advice.

In your case, you need to use sub-select. I changed column notation avoiding double quotes and blank spaces. Whatever your getfunction does, it must be in the outer query.

select 
user, 
Start_date , 
End_Date 
getfunction(u.user,Start_date,End_date) as Get_function
from
(
SELECT
u.USER,
u.SUBSCRIBE_DATE as Start_date,
(
    SELECT
    u.SUBSCRIBE_DATE    ( level - 1 ) * 91 as "fin"
    from DUAL
    connect by level <= (
        (
            ( CURRENT_DATE - u.SUBSCRIBE_DATE )   91
        ) / 91
    )
) as End_date
FROM User u
)