Create a primary key in the oracle View
A project requires a view to create a primary key. The following is an example.
CREATE or replace VIEW SME_V_A... (AGENTID, AGENTNAME, BUSYNUM, RESTNUM, RESTTIME, DEVICENONUM, DEVICENOSUM
, CONSTRAINT AGENTSTATIC_PK primary key (AGENTID) rely disable novalidate)
AS
Select a. AGENTID as AGENTID, --- Agent ID
(Select B. OPERATOR_NAME
FROM CC_AGENT_INFO B
Where B. WORK_NO = A. AGENTID) AS AGENTNAME, ---- agent name
NVL (SUM (C. BUSYNUM), 0) as busynum, ----- indicates the number of busy times
NVL (SUM (C. RESTNUM), 0) as restnum, ------ Number of breaks
NVL (SUM (C. RESTTIME), 0) as resttime, ------ rest duration (minutes)
NVL (D. DEVICENO_NUM, 0) as devicenonum, ------- total number of calls
NVL (D. DEVICENO_SUM, 0) as devicenosum ------- total session duration
FROM CC_V _...
Left join ......
Left join ......
Group by a. AGENTID, D. DEVICENO_NUM, D. DEVICENO_SUM;
Check whether a primary key exists.
select a.constraint_name, a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name = 'SME_V_A....'