Oracle Procedure - JOIN with Subquery

Question

I need some help finishing this procedure. The problem is how to alias the different SELECTs so that I can match them on the RIGHT OUTER JOIN. The traditional AS doesn't work. Any ideas?

PROCEDURE "GetProcessedEvents"
(
"In_OccurredFrom" TIMESTAMP,
"In_OccurredTo" TIMESTAMP,
"In_Interval" DECIMAL,
"Out_Cursor" OUT "Ref_Cursor"
)
IS
BEGIN    
OPEN "Out_Cursor" FOR      
  SELECT
    COUNT("Id") AS "ProcessedEvents",
    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"
  RIGHT OUTER JOIN
    (SELECT "Dts" FROM TABLE("F_GetDateIntervalTable"("In_OccurredTo", "In_OccurredFrom", "In_Interval")))
  ON "EventDts" = "Dts"
  WHERE
    "ProcessedDate" BETWEEN "In_OccurredFrom" AND "In_OccurredTo"
  GROUP BY
    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');
END "GetProcessedEvents";

Answer 1

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 

Answer 2

Try removing the double quotes around variable names, unless they are required by the syntax. That could be confusing the parser.

Answer 3

Yes, I got some ideas. Might be usefull using WITH clause, which allows you to break complex SELECT statements into subqueries which creatly improve structure of your code, and of course you can refer to them (subqueries) by alias.

Here is a small example of usage for you:

WITH subquery AS (SELECT sysdate FROM dual)
-- Here goes main query where subquery must be used
SELECT *
FROM subquery


OPEN "Out_Cursor" FOR     
WITH Q AS (SELECT "Dts" FROM TABLE("F_GetDateIntervalTable"("In_OccurredTo", "In_OccurredFrom", "In_Interval")))

SELECT
    COUNT("Id") AS "ProcessedEvents",
    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"
  RIGHT OUTER JOIN
    Q
  ON "EventDts" = "Dts"
  WHERE
    "ProcessedDate" BETWEEN "In_OccurredFrom" AND "In_OccurredTo"
  GROUP BY
    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');
END "GetProcessedEvents";