Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Using a CTE and cartesian product to populate table

BeefStuAug 17 2021

I have some code below, which is working fine that will generate date/times rows based on an interval.
My goal is to use those rows and combine them with a cartesian product of employees and locations in order to populate the access_history table.
For every date row there should be a combination of employee_id and location_id.
My question is do I need to combine the date CTE with the employee and location cartesian product in order to have all the values I need to populate the access_history table or is there a simpler way?
I would prefer to leave the date code alone so it not nested with any other information.
Below is my test CASE and a standalone CTE called 'combos', which I need to pull data from.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
Create table employees(
          employee_id NUMBER(6), 
          first_name VARCHAR2(20),
          last_name VARCHAR2(20),
         card_num VARCHAR2(10),
          work_days VARCHAR2(7)
       );

ALTER TABLE employees
        ADD (
          CONSTRAINT employees_pk PRIMARY KEY (employee_id)
              );

        INSERT INTO employees (
         employee_id,
         first_name, 
         last_name,
         card_num,
         work_days
        )
        WITH names AS   ( 
          SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
          SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
        SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
         SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
       ) SELECT * FROM names; 
    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'T' 
         END AS location_type
    FROM   dual
    CONNECT BY level <= 10;
     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));

create table access_history(
      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );

--- works fine --
WITH inputs ( value ) AS (
  SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
  FROM   DUAL
),
date_rows ( start_date, end_date ) AS (
  SELECT value,
         TRUNC(value) + INTERVAL '1' DAY
  FROM   inputs
UNION ALL
  SELECT start_date + INTERVAL '10' MINUTE,
         end_date
  FROM   date_rows
  WHERE  start_date + INTERVAL '10' MINUTE < end_date
)
SELECT start_date
FROM   date_rows;

-- need access to these rows to populate access_history table

with combos as
    ( select e.*, l.*
     from   employees e, locations l
    )

This post has been answered by Frank Kulash on Aug 17 2021
Jump to Answer

Comments

Processing

Post Details

Added on Aug 17 2021
3 comments
389 views