In my projet I need to use a function called getWorkingDay(beg_date Date,slider Integer). This function is used to find the n-th last working day from the date beg_date (where "n" is the value from slider).
What I would like to do is to get all of the working days within a date range defined by beg_date and end_date. I would like to use a SELECT statement and the function above to get the working days list.
I tried this statement with beg_date=sysdate-45 and end_date=sysdate :
SELECT * FROM(
SELECT getworkingDay(sysdate,slide) dt FROM DUAL
WHERE slide>0 and slide<=sysdate-(sysdate-45))
WHERE dt >=sysdate-45
I got the following error :
ORA-00904 : "SLIDE":invalid identifier
Could you help me to find the way to solve this problem?
Your function, getWorkingDays, only works for one, single date.
If you want it to work for a range of dates, then you need to call it once for each day in the date range.
Using the example from your question, sysdate - 45, means you need to call it 45 times, as in
select getWorkingDays(sysdate - 45, 1) from dual
union
select getWorkingDays(sysdate - 44, 1) from dual
union
select getWorkingDays(sysdate - 43, 1) from dual
I hope you get the idea. I substituted the value 1 (one) for your variable slider. You need to define it as a variable. You claim to be using SQL Developer so I suggest you refer to that product's documentation to see how to define variables.
Alternatively, you could write a SQL query that returns exactly 45 rows (again, 45 because that is the example you used in your question). I believe the following SO question shows how to achieve that:
SQL Query to return N rows from dual
Adapting the answer from that SO question, as well as the example from your question, I believe something like the following should work.
SELECT getWorkingDays(sysdate - (45 - LEVEL), 1)
FROM DUAL
CONNECT BY LEVEL <= 45
Of-course I can't really test it, since I don't have the code for function getWorkingdays().