Oracle Database Discussions

Hi,

Firstly I will apologise that I cannot provide too much information as my client is off line and remote. The SQL I will discuss works on both his test and my Oracle XE solution. But on live we get the folllowing error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."SUMINLINE$"."TEXT").

I have never posted to this forum but I cannot understand what the table SUMINLINE$ even does and it's relevance. Please also excuse if there is a better way to write the SQL), anyhow any guideance would be useful.

I thought it maybe down to how I am using rownum (this is a complete guess) but as it is materialized in the previous view I don't see how this could be)

Anyhow if anyone is good enough to spend some of there time looking at the SQL below I would be eternally grateful.

-----------------------------------------------------
STEP 1: I have a base materialised view:
(it uses the "rownum" as a primary key as a primary key is required later and this view doesn't have
a primary key as it is a complex view)
-------------------------------------------------------

CREATE MATERIALIZED VIEW bo_socrates.baseCSIIdentPerPerson
TABLESPACE bo_socrates
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1040K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
WITH PRIMARY KEY
AS
SELECT rownum iid,
csi.attendId,
csi.sceneId,
csi.caseId,
csi.identDate,
csi.identType,
csi.subIdenttype,
csi.suspectId,
csi.surname,
csi.forenames,
csi.CRO,
csi.ConfirmedIdent
FROM bus_objects.v_factCSIIdentPerPerson csi
;

CREATE INDEX bo_socrates.ind_baseCSIIdentPerson_Id on bo_socrates.baseCSIIdentPerPerson(id)
TABLESPACE bo_socrates
STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;


-----------------------------------------------------
STEP 2: This materialised view (my fact table) uses the previous materilalized view
as a point to calculate the fact date. Its aim for example the bottom "unit" is people
but I also need to know which people arrived first, which people arrived first with blue hats, etc.
So I use a with statements to find out these facts.
**PLEASE NOTE IT IS THIS VIEW THAT RAISES THE ERROR WHEN THE SCRIPT IS RUN**
If there is a simpler way to write this then please let me know
This view basically uses the primary key (id) generated in the above view using rownum
-------------------------------------------------------
CREATE MATERIALIZED VIEW bo_socrates.factCSIIdent
TABLESPACE bo_socrates
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1040K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
WITH PRIMARY KEY
AS
With
firstAttend As (
Select Attendid, Min(IdentDate) Identdate,
Min(Id) Id,
Min(Case When identtype = 0 Then Id Else Null End) Fpid,
Min(Case When identtype = 1 Then Id Else Null End) Dnaid,
Min(Case When identtype = 2 Then Id Else Null End) Fwid,
Min(Case When Confirmedident = 1 Then Id Else Null End) Idconfirm,
Min(Case When identtype = 0 And Confirmedident = 1 Then Id Else Null End) Fpidconfirm,
Min(Case When identtype = 1 And Confirmedident = 1 Then Id Else Null End) Dnaidconfirm,
Min(Case When identtype = 2 and confirmedident = 1 Then Id Else Null End) FwidConfirm
From Bo_Socrates.Basecsiidentperperson
GROUP BY Attendid),
firstInd AS (
Select Attendid, SuspectId, Min(IdentDate) Identdate, Min(Id) Id,
Min(Case When identtype = 0 Then Id Else Null End) Fpid,
Min(Case When identtype = 1 Then Id Else Null End) Dnaid,
Min(Case When identtype = 2 Then Id Else Null End) Fwid
From bo_socrates.BaseCSIIdentPerPerson
Group By Attendid, Suspectid)
Select csi.id,
csi.attendId,
csi.sceneId,
csi.caseId,
csi.identDate,
csi.identtype,
csi.subIdenttype,
csi.suspectId,
csi.surname,
csi.forenames,
csi.CRO,
Csi.Confirmedident,
Case When Ind.Id Is Null Then 0 Else 1 End Firstindident,
Case When Ind.Fpid Is Null Then 0 Else 1 End Firstindidentfp,
Case When Ind.DNAid Is Null Then 0 Else 1 End FirstindidentDNA,
Case When Ind.FWid Is Null Then 0 Else 1 End FirstIndIdentFW,
Case When Att.Id Is Null Then 0 Else 1 End Firstattident,
Case When Attfp.Fpid Is Null Then 0 Else 1 End Firstattidentfp,
Case When AttDNA.DNAid Is Null Then 0 Else 1 End FirstattidentDNA,
Case When Attfw.Fwid Is Null Then 0 Else 1 End Firstattidentfw,
Case When Att.IdConfirm Is Null Then 0 Else 1 End FirstattidentConfirm,
Case When Attfpc.Fpidconfirm Is Null Then 0 Else 1 End Firstattidentfpconfirm,
Case When AttDNAc.DNAidconfirm Is Null Then 0 Else 1 End FirstattidentDNAconfirm,
Case When AttFWc.FWidconfirm Is Null Then 0 Else 1 End FirstattidentFWconfirm
From Bo_Socrates.Basecsiidentperperson Csi
Left Outer Join Firstind Ind On Csi.Id = Ind.Id
Left Outer Join Firstind Indfp On Csi.Id = Indfp.Fpid
Left Outer Join Firstind Inddna On Csi.Id = Inddna.Dnaid
Left Outer Join Firstind IndFW On Csi.Id = IndFW.FWid
Left Outer Join Firstattend Att On Csi.Id = Att.Id
Left Outer Join Firstattend Attfp On Csi.Id = Attfp.Fpid
Left Outer Join Firstattend Attdna On Csi.Id = Attdna.Dnaid
Left Outer Join Firstattend Attfw On Csi.Id = Attfw.Fwid
Left Outer Join Firstattend Attfpc On Csi.Id = AttFPc.Fpidconfirm
Left Outer Join Firstattend Attdnac On Csi.Id = AttDNAc.DNAidconfirm
Left Outer Join Firstattend Attfwc On Csi.Id = AttFWc.Fwidconfirm
;


CREATE INDEX bo_socrates.ind_factCSIIdent_Id on bo_socrates.factCSIIdent(attendId)
TABLESPACE bo_socrates
STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;

CREATE INDEX bo_socrates.ind_factCSIIdent_date on bo_socrates.factCSIIdent(identDate)
TABLESPACE bo_socrates
STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;

CREATE INDEX bo_socrates.ind_factCSISubIdent_type on bo_socrates.factCSIIdent(subidentType)
TABLESPACE bo_socrates
STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;


COMMIT;