Hi everyone,
was just wondering if somebody could provide me with some suggestions other than using PL/SQL (which I've already implemented successfully, I just want to know if this is even possible).
What I want to do is query a table multiple times (arbitraryly n times).
For example, I have a simple query:
select *
from tab sample(40);
I wish to do this for each day in a given month, with the sample function being called for each day(semi-psudo code):
with dat as (
select dt
from (
select to_date('01-07-2015', 'DD-MM-YYYY') + rownum -1 dt
from dual
connect by level <= to_date('31-07-2015', 'DD-MM-YYYY') - to_date('01-07-2015', 'DD-MM-YYYY') + 1
)
where to_char(dt, 'fmday') not in ('sunday','saturday')
)
select *
from tab sample(40)
I can't use a cross join because the same sample would be use for each day. Essentially, I want a different sample taken every day.
Can you think of a way to do this strictly within SQL?