I wonder if someone can help me with adding a row into a view in Oracle

I have a view and what I want to do is add a blank row with id and name if that particular ID is missing an event from a list of event which is in field called event.

The columns in the view are id, name, date, event, status. Each person should complete number of events from the list but if they are missing an event I want to insert a row with all the details with a text saying missing event in the event column for that particular id.

I haven't done this before and don't know where to make a start. Can someone guide on this please?.

select id,name,date,event,status from status

CodePudding user response：

The way I understood it, it is an outer join along with NVL (or coalesce; pick one you prefer).

Here's an example based on Scott's schema (as I don't have your tables):

There are departments 30 and 40:

SQL> SELECT *
  2    FROM dept
  3   WHERE deptno IN (30, 40);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Nobody works in department 40:

SQL> SELECT deptno, empno, ename
  2    FROM emp
  3   WHERE deptno IN (30, 40);

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        30       7499 ALLEN
        30       7521 WARD
        30       7654 MARTIN
        30       7698 BLAKE
        30       7844 TURNER
        30       7900 JAMES

6 rows selected.

View, as join of these two tables, should be outer join (otherwise you wouldn't get department 40). NVL is used to "populate" missing column value:

SQL> CREATE OR REPLACE VIEW v_emp
  2  AS
  3     SELECT d.deptno,
  4            d.dname,
  5            e.empno,
  6            NVL (e.ename, 'Missing person') ename
  7       FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno
  8      WHERE d.deptno IN (30, 40);

View created.

Result:

SQL>   SELECT *
  2      FROM v_emp
  3  ORDER BY deptno;

    DEPTNO DNAME               EMPNO ENAME
---------- -------------- ---------- --------------
        30 SALES                7499 ALLEN
        30 SALES                7521 WARD
        30 SALES                7654 MARTIN
        30 SALES                7698 BLAKE
        30 SALES                7844 TURNER
        30 SALES                7900 JAMES
        40 OPERATIONS                Missing person       --> this

7 rows selected.

SQL>

Now, try to apply that to your case.

CodePudding user response：

Assuming you have the tables:

CREATE TABLE events (
  id   NUMBER PRIMARY KEY,
  name VARCHAR2(20) UNIQUE
);

CREATE TABLE activities (
  id       NUMBER,
  name     VARCHAR2(20),
  "DATE"   DATE,
  team     VARCHAR2(20),
  event_id REFERENCES events (id),
  status   VARCHAR2(20)
);

Then you can create the view using a partitioned outer join:

CREATE VIEW view_name (id, name, "DATE", team, event, status) AS
SELECT a.id,
       a.name,
       a."DATE",
       a.team,
       e.name,
       a.status
FROM   events e
       LEFT OUTER JOIN activities a
       PARTITION BY (a.id, a.name)
       ON (e.id = a.event_id)

Then, for the sample data:

INSERT INTO events (id, name)
SELECT 1, 'Shotput'   FROM DUAL UNION ALL
SELECT 2, 'Javelin'   FROM DUAL UNION ALL
SELECT 3, 'Discus'    FROM DUAL;

INSERT INTO activities (id, name, "DATE", team, event_id, status)
SELECT 1, 'Alice', DATE '1970-01-01', 'A-Team', 1, 'accepted' FROM DUAL UNION ALL
SELECT 1, 'Alice', DATE '1970-01-02', 'A-Team', 2, 'accepted' FROM DUAL UNION ALL
SELECT 1, 'Alice', DATE '1970-01-03', 'A-Team', 3, 'declined' FROM DUAL UNION ALL
SELECT 2, 'Betty', DATE '1970-01-01', 'A-Team', 1, 'accepted' FROM DUAL UNION ALL
SELECT 2, 'Betty', DATE '1970-01-02', 'A-Team', 3, 'declined' FROM DUAL;

Then the view contains:

SELECT * FROM view_name;

ID	NAME	DATE	TEAM	EVENT	STATUS
1	Alice	01-JAN-70	A-Team	Shotput	accepted
1	Alice	02-JAN-70	A-Team	Javelin	accepted
1	Alice	03-JAN-70	A-Team	Discus	declined
2	Betty	01-JAN-70	A-Team	Shotput	accepted
2	Betty	null	null	Javelin	null
2	Betty	02-JAN-70	A-Team	Discus	declined

db<>fiddle here