Forum Stats

  • 3,769,237 Users
  • 2,252,936 Discussions
  • 7,874,955 Comments

Discussions

Using a CTE and cartesian product to populate table

BeefStu
BeefStu Member Posts: 284 Blue Ribbon

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
    )


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,222 Red Diamond
    Accepted Answer

    Hi, @BeefStu

    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?

    So, for each combination of the 4 employees and 10 locations, you want to insert 33 rows into access_history - one for each row in date_rows: a total of 4 * 10 * 33 = 1320. Is that right?

    If so, a Cartesian product is what you want. Given that you want to use the date_rows "table" as it is, here's one way:

    INSERT INTO access_history (employee_id, card_num, location_id, access_date)
    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     e.employee_id
    , 	   e.card_num
    ,	   l.location_id
    ,	   d.start_date
    FROM	   employees e
    CROSS JOIN locations l
    CROSS JOIN date_rows d
    ;
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,222 Red Diamond
    Accepted Answer

    Hi, @BeefStu

    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?

    So, for each combination of the 4 employees and 10 locations, you want to insert 33 rows into access_history - one for each row in date_rows: a total of 4 * 10 * 33 = 1320. Is that right?

    If so, a Cartesian product is what you want. Given that you want to use the date_rows "table" as it is, here's one way:

    INSERT INTO access_history (employee_id, card_num, location_id, access_date)
    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     e.employee_id
    , 	   e.card_num
    ,	   l.location_id
    ,	   d.start_date
    FROM	   employees e
    CROSS JOIN locations l
    CROSS JOIN date_rows d
    ;
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,222 Red Diamond

    By the way:

      CASE round(dbms_random.value(1,3)) 

          WHEN 1 THEN 'A' 

          WHEN 2 THEN 'T' 

          WHEN 3 THEN 'T' 

         END AS location_type

    tends to set assign 'A' to 1/4 of the rows, and 'T' to the remaining 3/4 of the rows.

    If you want to assign 'A' to 1/3 of the rows, and 'T' to the reaming 2/3 of the rows, then you can use:

    CASE
      WHEN dbms_random.value (0, 3) < 1
      THEN 'A'
      ELSE 'T'
    END AS location_type
    


  • BeefStu
    BeefStu Member Posts: 284 Blue Ribbon

    @Frank Kulash perfect that is exactly what I wanted. Thanks once again for your help and expertise.