AS is optional when aliasing columns, but not allowed at all by Oracle when aliasing tables, including subqueries. So give the subquery a name, just don't include the AS keyword:
FROM "Dat_ScheduledEvent" alias1
RIGHT OUTER JOIN (
SELECT "Dts"
FROM TABLE("F_GetDateIntervalTable"("In_OccurredTo", "In_OccurredFrom",
"In_Interval"))
) alias2
But you have some other problems. Firstly, creating objects with identifiers that have to be enclosed in double quotes is painful to work with; is having mixed-case identifier really worth it? Secondly, it looks like EventDts is only a derived field, and a column alias can't be used anywhere else in the query other than an order by clause, so you probably need to make that a subquery too - which means you can use it in the group by too. Thirdly, DATEDIFF is not a built-in Oracle function - unless you've created that yourself you'll need to use something else.
SELECT
COUNT(alias1."Id") AS "ProcessedEvents",
alias1."EventDts"
FROM (
SELECT "Id",
TO_TIMESTAMP('1900-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF')
+ NUMTODSINTERVAL(FLOOR(DATEDIFF('MI', '1900-01-01', "ProcessedDate")
/"In_Interval")*"In_Interval", 'MINUTE') AS "EventDts"
FROM "Dat_ScheduledEvent"
WHERE "ProcessedDate" BETWEEN "In_OccurredFrom" AND "In_OccurredTo"
) alias1
RIGHT OUTER JOIN (
SELECT "Dts"
FROM TABLE("F_GetDateIntervalTable"("In_OccurredTo", "In_OccurredFrom",
"In_Interval"))
) alias2
ON alias1."EventDts" = alias2."Dts"
GROUP BY alias2."EventDts";
DATEDIFF still needs to be replaced, but I'm not quite sure what you're doing. If the F_GetDateIntervalTable is generating suitable intervals, I'm not sure why you need to do that at all; don't you want to find ProcessedDate values within the intervals, i.e. >=Dtand <Dt+ InInterval? Depending on what InInterval actually represents, of course.
Assuming F_GetDateIntervalTable gives you the start of each interval within the period of interest, you can do something like this instead:
PROCEDURE "GetProcessedEvents"
(
"In_OccurredFrom" TIMESTAMP,
"In_OccurredTo" TIMESTAMP,
"In_Interval" DECIMAL,
"Out_Cursor" OUT "Ref_Cursor"
)
IS
BEGIN
OPEN "Out_Cursor" FOR
SELECT
COUNT(Event."Id") AS "ProcessedEvents",
DateInt."Dts"
FROM (
SELECT "Dts", COALESCE(LEAD("Dts")
OVER (ORDER BY "Dts"), "In_OccurredTo") as "NextDts"
FROM TABLE("F_GetDateIntervalTable"("In_OccurredTo", "In_OccurredFrom",
"In_Interval"))
) DateInt
LEFT JOIN "Dat_ScheduledEvent" Event
ON Event."ProcessedDate" >= DateInt."Dts"
AND Event."ProcessedDate" < DateInt."NextDts"
GROUP BY DateInt."Dts"
ORDER BY DateInt."Dts";
END "GetProcessedEvents";
/
The LEAD() lets you peek at the next result, so that subquery is converting a list of timestamps into a list of from- and to-timestamps (with the last one ending at In_OccurredTo), which allows you to look for matching records in your data table that fall within those ranges - no manipulation of the ProcessedDate is needed.
With some fake data:
create table "Dat_ScheduledEvent" ("Id" number, "ProcessedDate" timestamp);
insert into "Dat_ScheduledEvent" ("Id", "ProcessedDate")
values (1, to_timestamp('2013-02-06 09:45', 'YYYY-MM-DD HH24:MI'));
insert into "Dat_ScheduledEvent" ("Id", "ProcessedDate")
values (1, to_timestamp('2013-02-06 09:50', 'YYYY-MM-DD HH24:MI'));
insert into "Dat_ScheduledEvent" ("Id", "ProcessedDate")
values (1, to_timestamp('2013-02-06 10:15', 'YYYY-MM-DD HH24:MI'));
... and calling the procedure from SQL*Plus:
var r refcursor;
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI';
exec "GetProcessedEvents"(to_date('2013-02-06 08:00'), to_date('2013-02-06 12:00'), 1/24, :r);
print :r
... I get:
ProcessedEvents Dts
---------------------- -------------------------
0 06-FEB-13 08.00.00.000000000
2 06-FEB-13 09.00.00.000000000
1 06-FEB-13 10.00.00.000000000
0 06-FEB-13 11.00.00.000000000
