SQL expresions

Syntaks: (Tabelnavne skal have leading spce, så MYSQ fkt. kan
kvalificere dem)
SQL expresions
'***********Udfør en SQL som action query. RUNSQL behøver ikke parameter substitution
Call sqlRecset(8, "", "", "", "") ' delete * from TempObjektID_T;
MsgBox "Action Query udført. Tjek at TempObjektID_T er tømt"
'************Hent et recordset fra en selct sætning. Recordst .open kræver parameter substitution.
Set st = sqlRecset(6, "", [Forms]![soeg_f]![Karakteristika], [Forms]![soeg_f]![fritekst], [Forms]![soeg_f]![ObjID])
'BEMÆRK ObjID skal omdannes til tal med VAL(string) i SQL udtrykket
'omskrevet:sqlRecset(6, "", "*", "*", str(102)) eller uden VAL i SQL: sqlRecset(6, "", "*", "*", "#" & str(102))
st.MoveFirst
MsgBox "enkrlt felt i første post: " & st(0)
'******************Visning af SQLstringReturn:
MsgBox "Visning af SQLstringReturn: " & SQLstringReturn
'***************Hent en enkelt værdi********
stri = sqlRecset(37, "", str(410), "", "")(0)
MsgBox "Enkelt værdi til string eller number: " & (stri)
'****************Erstat SQL udtryk; tabeller select, from og where clause*****
'Call sqlRecset(x, "=eventueltNavn", "", "", "")
'= foran naet eventuelt navn i navnefeltet, medfører at kun SQL teksten returneres
'eksempel:
'Call sqlRecset(114, "=", ToBase, "", "") 'SQL streng returneres med Param1 substitueret med værdien af ToBase
'Me.RecordSource = SQLstringReturn --en Formular får åbnings sql
'Call sqlRecset(x, "eventueltNavn", "#Tabelnavn", "", "","" ,"", "")
'# foran parameter strengen, fortæller at det er et udtryk der skal substitueres uden citationstegn.
'F.eks. et tal eller
' f.eks et tabelnavn
ID
SQL udtryk
Ver.:20120808
SQL type
1
Evt. navn
Forklaring
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
5
SELECT * from facttable1_t, gereba_node1.tempobjektid_t as t where
t.objektid=&Param1& and t.objektid=fremsoegtid;
SELECT
TEST sql til
strenge
null
null
6
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2
where
f1.MedKarakteristika Like &Param1&
and f2.DATA like &Param2&
and f1.FremsoegtID= f2.FremsoegtID
UNION SELECT f1.FremsoegtID as id
FROM Facttable1_t as f1
where f1.fremsoegtID=val(&Param3&)
and &Param2&="0"
SELECT
Test_select
Test på substitution af
parametre som
kræves i SELECT
udførsler
Test på kald
af Function
sqlRecset,
kaldt fra Sub
Test_sqlRecs
et()
8
delete from TempObjektID_T;
DELETE
SletTempObj
null
Før nye
søgninger
med
ACCESS, se
også 301 for
PHP. 8
bruges også i
PHP ved
cleaning fra
Builtfacttable
9
insert into TempObjektID_T (ObjektID) select id from TEMP2_q
INSERT
ResultIntoTemp
null
Søgeformen
14
select def1, def2, deftext from gereba_node1.Mandatory_T where def1=&Param1&
SELECT
Test
null
null
19
INSERT into StaggingPD2_T
(Action,FromBase, ToBase, Karakteristika,
KarakType, DATA, ObjektType,
INSERT
StaggingPD2_Q
Param1 er basenavn
null
Ver.:20120808
2
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
LocalTransOK, GlobalTransOK,LocalID, NewID)
SELECT &Param4&, &Param1&, &Param2&, c.fieldx, f.definition , f.data,
c.ObjektTypeX, 1, 0, t.objektid, 0
FROM facttable1_t AS f, convert_t AS c, tempobjektid_t AS t
WHERE c.field=f.medkarakteristika
and c.databasex=&Param2&
and t.objektid=&Param3&
and t.objektid=f.fremsoegtid
and f.def=c.ObjektType;
22
SELECT oo.objekt1,
f1.fremsoegtid,
f1.medkarakteristika,
f1.DATA,
f2.fremsoegtid
FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo
WHERE f2.def="ProcObj"
and oo.objekt2=f2.fremsoegtID
and oo.relationstype="ActivateDok"
and oo.objekt1=f1.fremsoegtID
and f2.medkarakteristika="Alert"
and format( f2.DATA,"yyyymmddhhmm")<=Format(Now, "yyyymmddhhmm") ;
SELECT
Alert_Q
Bemærk Alert
null
karakteristika og
tidspunkt skal være på
ProcObj
eRelationstypen
sættes eller slettes ?
23
INSERT into TempObjektID_T (ObjektID,user)
SELECT oo.objekt1,"admin"
FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo
WHERE f2.def="ProcObj"
and oo.objekt2=f2.fremsoegtID
and oo.relationstype="ActivateDok"
and f1.fremsoegtID=oo.objekt1
and f1.medkarakteristika="Alert"
and f2.medkarakteristika="Alert"
and format(f1.DATA, "yyyymmdd")<=format(Now, "yyyymmdd");
INSERT
null
null
Ver.:20120808
3
Report created:
null
14-01-2015
ID
24
SQL udtryk
SELECT o.Def, "noconvert" AS Udtryk1, o.id
FROM objdeclare_t AS o, TempObjektID_T AS toi
WHERE (o.ID=toi.objektid)
AND not Exists (SELECT null
FROM facttable1_t AS f, convert_t AS c
WHERE c.field=f.medkarakteristika
and c.databasex=&Param1&
and f.fremsoegtid=toi.objektid
and o.def=c.ObjektType
)
AND not Exists (SELECT null
FROM convert_t AS c
WHERE c.field="copy"
and c.databasex=&Param1&
)
union
SELECT o.Def, "convertok" AS Udtryk1 , o.id
FROM objdeclare_t AS o, TempObjektID_T AS toi
WHERE (o.ID=toi.objektid)
AND Exists (SELECT null
FROM facttable1_t AS f, convert_t AS c
WHERE c.field=f.medkarakteristika
and c.databasex=&Param1&
and f.fremsoegtid=toi.objektid
and o.def=c.ObjektType
)
union
SELECT "copy", "copy", 0
FROM convert_t AS c
WHERE c.field="copy"
and c.databasex=&Param1&
;
Ver.:20120808
4
SQL type
Evt. navn
Forklaring
SELECT
TjekObjektConve Tjekker at der fineds null
rt
en type konverterings
angivelse i convert_t
og om der er
konvertering af felter.
Bemærk den tjekker
kun om der eksistere
mindst ét felt, ELLER
databasen der
eksporteres til er en
base med samme
objekt typer COPY
Report created:
14-01-2015
Benyttet hvor
ID
25
SQL udtryk
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4
where
f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika]
and f1.definition="DATA"
and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt]
and f3.DATA like [Forms]![soeg_f]![fritekst]
and f4.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
and f3.fremsoegtID=f4.fremsoegtID
UNION ALL
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3
where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or
f1.data=[forms]![Soeg_F]![thesaurus_felt])
and f1.definition="EndDef"
and f2.DATA like [Forms]![soeg_f]![fritekst]
and f3.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
UNION all
SELECT ObjDeclare_T.id
FROM ObjRelationObj_T, ObjDeclare_T
where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or
ObjDeclare_T.id=ObjRelationObj_T.objekt2)
and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2]
and [Forms]![soeg_f]![fritekst]="0"
UNION all
select oro.objekt2
From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2
where f1.data like [Forms]![soeg_f]![fritekst]
and f1.fremsoegtid=oro.objekt2
Ver.:20120808
5
SQL type
Evt. navn
Forklaring
SELECT VIEW
Temp2_Q
Fremsøger med
omskrevet til
enkeltfelt fritekst, uden SQL 230,
Og, Eller.
231, 232
BENYTTES IKKE
Søgning Karakteristika
eller thesaurus med
endDef
Søgning på relations
typer
Thesaurus søgning
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
and oro.objekt1=f2.fremsoegtid
and [forms]![Soeg_F]![thesaurus_felt]<>"*"
and [forms]![Soeg_F]![fritekst]<>"*"
and f2.data like [forms]![Soeg_F]![thesaurus_felt]
UNION all
SELECT f1.FremsoegtID as id
FROM Facttable1_t as f1
where f1.fremsoegtID=val([forms]![Soeg_F]![ObjID])
and [forms]![Soeg_F]![fritekst]="0"
;
26
Benyttet hvor
ID søgning
INSERT into TempObjektID_T (user, ObjektID) select "admin", u.id from
(SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as
f4,Facttable1_t as f5
where
f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika]
and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt]
and f3.DATA like [Forms]![soeg_f]![fritekst] and f5.DATA like
[Forms]![soeg_f]![Fritekst2]
and f4.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
and f3.fremsoegtID=f4.fremsoegtID
and f4.fremsoegtID=f5.fremsoegtID
UNION ALL
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3
where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or
f1.data=[forms]![Soeg_F]![thesaurus_felt])
and f1.definition="EndDef"
and f2.DATA like [Forms]![soeg_f]![fritekst]
and f3.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
Ver.:20120808
Forklaring
6
INSERT
TEMP2_q_and
Fremsøger hvor Og
operator er brugt.
Report created:
14-01-2015
null
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
INSERT
TEMP2_q_or
null
null
and f2.FremsoegtID= f3.FremsoegtID
UNION ALL
SELECT ObjDeclare_T.id
FROM ObjRelationObj_T, ObjDeclare_T
where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or
ObjDeclare_T.id=ObjRelationObj_T.objekt2)
and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2]
and [Forms]![soeg_f]![fritekst]="0"
UNION ALL
select f1.fremsoegtid
From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2, facttable1_t as f3
where f1.data like [Forms]![soeg_f]![fritekst]
and f1.fremsoegtid=oro.objekt2
and f2.data like [Forms]![soeg_f]![fritekst2]
and f1.fremsoegtid=f2.fremsoegtid
and oro.objekt1=f3.fremsoegtid
and [forms]![Soeg_F]![thesaurus_felt]<>"*"
and [forms]![Soeg_F]![fritekst]<>"*"
and [forms]![Soeg_F]![fritekst2]<>"*"
and f3.data like [forms]![Soeg_F]![thesaurus_felt]
UNION SELECT f1.FremsoegtID as id
FROM Facttable1_t as f1
where f1.fremsoegtID=[forms]![Soeg_F]![ObjID]
and [forms]![Soeg_F]![fritekst]="0") as u;
27
INSERT into TempObjektID_T (user, ObjektID) select "admin", u.id from
(SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as
f4,Facttable1_t as f5
where
f1.MedKarakteristika Like [forms]![Soeg_F]![Karakteristika]
and f2.MedKarakteristika Like [forms]![Soeg_F]![thesaurus_felt]
and (f3.DATA like [Forms]![soeg_f]![fritekst] or f5.DATA like
[Forms]![soeg_f]![Fritekst2])
Ver.:20120808
7
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
and f4.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
and f3.fremsoegtID=f4.fremsoegtID
and f4.fremsoegtID=f5.fremsoegtID
UNION ALL
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3
where (f1.DATA = [forms]![Soeg_F]![Karakteristika] or
f1.data=[forms]![Soeg_F]![thesaurus_felt])
and f1.definition="EndDef"
and f2.DATA like [Forms]![soeg_f]![fritekst]
and f3.Def Like [forms]![Soeg_F]![combo12]
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
UNION ALL
SELECT ObjDeclare_T.id
FROM ObjRelationObj_T, ObjDeclare_T
where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or
ObjDeclare_T.id=ObjRelationObj_T.objekt2)
and ObjRelationObj_T.relationstype= [forms]![Soeg_F]![combo2]
and [Forms]![soeg_f]![fritekst]="0"
UNION ALL
select f1.fremsoegtid
From objrelationobj_t as oro, facttable1_t as f1, facttable1_t as f2, facttable1_t as f3
where f1.data like [Forms]![soeg_f]![fritekst]
and f1.fremsoegtid=oro.objekt2
and f2.data like [Forms]![soeg_f]![fritekst2]
and f1.fremsoegtid=f2.fremsoegtid
and oro.objekt1=f3.fremsoegtid
and [forms]![Soeg_F]![thesaurus_felt]<>"*"
and [forms]![Soeg_F]![fritekst]<>"*"
and [forms]![Soeg_F]![fritekst2]<>"*"
and f3.data like [forms]![Soeg_F]![thesaurus_felt]
Ver.:20120808
8
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
INSERT
CreateFactTable
Opbygger
facttable1_t., før
prioritet indsættes med
sql 88.
DENNE SQL virker
ikke med UNION i
ODBC, derfor er den
opsplittet i 3 INSERT
(212,213,214) Nu
bygget til PHP
Alle kald til
BuildFactTab
le(ObjID As
Long) med 0
som
argument,
hvorved hele
tabellen
gendannes
UNION ALL SELECT f1.FremsoegtID as id
FROM Facttable1_t as f1
where f1.fremsoegtID=[forms]![Soeg_F]![ObjID]
and [forms]![Soeg_F]![fritekst]="0") as u;
28
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id as ID,
"U2" as unionnr,
o.def,
data_t.type as medkarakteristika,
data_t.data,
ucase("DATA") as definition
from objdeclare_t o,
data_t,
(select data_id, objekt_id from relationdata_t) ra
where o.id=&Param1&
and ra.objekt_id=o.id
and ra.data_id=data_t.data_id
and data_t.data<>data_t.type
UNION
select o.id as ID,
"U1" as unionnr,
o.def,
data_t.type as karakteristika,
data_t.data,
"EndDef" as definition
from objdeclare_t o,
data_t,
(select data_id, objekt_id from relationdata_t) ra
where o.id=&Param1&
and ra.objekt_id=o.id
and ra.data_id=data_t.data_id
and data_t.data=data_t.type
UNION select o.id, "U3",
Ver.:20120808
9
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
o.def,
&Param2&,
concat(dbi.BasisArkiv, d2.data, d.data) as sti,
&Param3&
from objdeclare_t o, relationData_t rd, relationData_t rd2,
data_t d, data_t d2, DBInstans_t as dbi
where o.id=&Param1&
and o.id=rd.objekt_id
and o.id=rd2.objekt_id
and o.def="Dokument"
and rd.data_id=d.data_id
and rd2.data_id=d2.data_id
and d.type="Filnavn"
and d2.type="FilKatalog"
and dbi.id= &Param4&;
30
select fremsoegtid, def, medkarakteristika, data from facttable1_t order by fremsoegtid
desc
SELECT
null
null
null
31
select fremsoegtid, def, medkarakteristika, data from facttable1_t, TempObjektID_T
where fremsoegtID=[TempObjektID_T].[objektid]
SELECT
null
null
null
33
SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk, prioritet
from Dataregler_T dr,
definitionshiraki_T a,
definitionshiraki_T x
where dr.ObjektKlasse= a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION
SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk,prioritet
from Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
SELECT VIEW
selectDataRegler Danner krævede
KarakRela_Q
/ønskede
karakteristika udfra et
vilkårligt niveau i
objekt definitions
hirakiet.
Ver.:20120808
10
Report created:
14-01-2015
Bruges ikke
som select,
men som
INSERT i 34
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
SELECT
SelectDataRegle Indsætter krævede
rKarak_Q
/ønskede
Benyttet hvor
definitionshiraki_T x
where dr.ObjektKlasse= b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION
SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2, dr.Obligatorisk,prioritet
from Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
definitionshiraki_T c,
definitionshiraki_T x
where dr.ObjektKlasse= c.def1
and c.def2=b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION SELECT dr.ObjektKlasse, a.def1, dr.TilladtKarakRela, x.def2,
dr.Obligatorisk,prioritet
from Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
definitionshiraki_T c,
definitionshiraki_T d,
definitionshiraki_T x
where dr.ObjektKlasse= d.def1
and d.def2=c.def1
and c.def2=b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1;
34
SELECT "-", "-", "-", u.adef1, u.drt, u.xdef2, "", convert(&Param1&,signed), 0
FROM (SELECT dr.ObjektKlasse as drobj, a.def1 as adef1,
Ver.:20120808
11
Report created:
14-01-2015
null
ID
SQL udtryk
SQL type
dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed)
Dataregler_T dr,
definitionshiraki_T a,
definitionshiraki_T x
where dr.ObjektKlasse= a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION
SELECT dr.ObjektKlasse, a.def1 as adef1,
dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed)
from Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
definitionshiraki_T x
where dr.ObjektKlasse= b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION
SELECT dr.ObjektKlasse, a.def1 as adef1,
dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed)
from Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
definitionshiraki_T c,
definitionshiraki_T x
where dr.ObjektKlasse= c.def1
and c.def2=b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
UNION
SELECT dr.ObjektKlasse, a.def1 as adef1,
dr.TilladtKarakRela as drt, x.def2 as xdef2, "", convert(&Param1&,signed)
from
Ver.:20120808
12
Evt. navn
Forklaring
karakteristika udfra et
vilkårligt niveau i
objekt definitions
hirakiet.
Med Val(param) er det
i access sql, her i
MYSQL med convert
(er opspliitet i sql 216
ff, for at køre med
odbc)
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
from
Dataregler_t dr,
definitionshiraki_t b,
definitionshiraki_T a,
definitionshiraki_T c,
definitionshiraki_T d,
definitionshiraki_T x
where dr.ObjektKlasse= d.def1
and d.def2=c.def1
and c.def2=b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
) AS u
WHERE u.adef1=&Param2&;
35
INSERT into StaggingPD2_T
(Action,FromBase, ToBase, Karakteristika,
KarakType, DATA, ObjektType,
LocalTransOK, LocalID)
SELECT "EditObject" ,&Param2&,&Param3&, f.medkarakteristika,
f.definition, f.data, f.def,
false, f.fremsoegtid
FROM facttable1_t AS f
WHERE f.fremsoegtid=&Param1&
and UnionNr<>"U3"
and f.definition="DATA"
;
INSERT
RedigPrepare1_
Q
Indsættelse af DATA null
hvor
medkarakteristika=DA
TA. Hvis EndDef er
det nødvendigt at
insætte
MedKarakteristika =
DATA, fordi facttablen
nu viser et højere
niveau af enddef
karakteristika. (se 185)
37
select def from ObjDeclare_T where id=&Param1&
SELECT
FindDef
null
38
select ObjektType,
Karakteristika, KarakType, DATA,
NewID, LocalID, transactID
SELECT
GetRedig_Q
Henter data fra
null
stagging, hvor data er,
eller ikke er testede,
Ver.:20120808
13
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
From StaggingPD2_T
where GlobaltransOK=FALSE
and action =&Param2&
and tobase=&Param1&;
Forklaring
Benyttet hvor
og endnu ikke indsat i
basen
40
INSERT into StaggingPD2_T
(FromBase, Tobase, Action, ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID)
select &Param3&, &Param3&, "EditObject", &Param2&,"RegistDato",
"DATA",&Param4& ,&Param1&,&Param1&;
INSERT
InsertDato_Q
Indsætter dags dato, null
BEMÆRK det er en
fejl at RegistDato er
hårdtkodet, og at ordre
også må indsættes
hårdtkodet p.g.af
mangel på parametre
45
insert into temptemp2 (last) SELECT distinct oo.objekt2
from ObjRelationObj_T as oo, TempObjektID_T
where oo.Objekt1=TempObjektID_T.objektid;
INSERT
RightObjects
null
null
46
insert into TempObjektID_T (objektID, user)
select last, "admin" from temptemp2;
INSERT
null
null
null
47
delete from temptemp2;
DELETE
null
null
null
48
insert into ObjRelationObj_T (Objekt1, Relationstype, Objekt2)
select val(&Param1&) , "ActivateDok", FremsoegtID
from Facttable1_t
where MedKarakteristika="Alert" and def="ProcObj";
INSERT
InsertAlert
null
null
49
SELECT f1.FremsoegtID, f1.MedKarakteristika, f1.Def, "Alert Eksisterer, OK"
FROM FactTable1_T AS f1
WHERE f1.Def="ProcObj" and f1.medkarakteristika="Alert";
union
SELECT "", "","", "Alert Eksisterer ikke, Advarsel!"
FROM FactTable1_T AS f1
WHERE not exists ( select null from FactTable1_T AS f1 where f1.Def="ProcObj" and
SELECT
TestForEksistens null
ALERT
null
Ver.:20120808
14
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
f1.medkarakteristika="Alert")
SELECT "", "","", "NyopretObj Eksisterer ikke, Advarsel!"
FROM FactTable1_T AS f1
WHERE not exists ( select null from FactTable1_T AS f1 where f1.Def="ProcObj" and
f1.medkarakteristika="NyopretObj")
;
50
Delete * from ObjRelationObj_T where objekt1=val(&Param1&)
and relationstype='ActivateDok'
and objekt2=(select f.fremsoegtID from facttable1_t as f
where f.def='ProcObj'
and Medkarakteristika='Alert');
DELETE
null
null
null
52
INSERT into TempObjektID_T (ObjektID,user)
SELECT distinct oo.objekt1,"admin"
FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo
WHERE f2.def="ProcObj"
and oo.objekt2=f2.fremsoegtID
and oo.relationstype="ActivateDok"
and f1.fremsoegtID=oo.objekt1
and f2.medkarakteristika="Alert"
and format(f2.DATA, "yyyymmdd")>format(Now, "yyyymmdd");
INSERT
ShowAlerts_Q
viser alle Alerts der
har forekommet
null
53
INSERT into temptemp2 (def2, first)
select str(u.id), left(u.dat,10) from
(
SELECT FactTable1_T.FremsoegtID as id,
FactTable1_T.DATA as dat
FROM TempObjektID_T, FactTable1_T
where TempObjektID_T.ObjektID = FactTable1_T.FremsoegtID
and str(FactTable1_T.prioritet)=str(TempObjektID_T.sort)
union
SELECT FactTable1_T.FremsoegtID ,FactTable1_T.DATA
FROM TempObjektID_T , FactTable1_T
INSERT
TempSort_Q
indsætter id som string
Sortering er ligegyldig
her., foregår i
fremsøgnings
rapporterne
Fra
sorterings
knappen i
søg
Ver.:20120808
15
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
where TempObjektID_T.ObjektID = FactTable1_T.FremsoegtID
and FactTable1_T.prioritet = (select min(prioritet) from FactTable1_T as f1
where f1.fremsoegtID=TempObjektID_T.ObjektID )
and not exists (select null from FactTable1_T as f2
where str( f2.prioritet)=str(TempObjektID_T.sort)
and f2.fremsoegtID=TempObjektID_T.ObjektID
) order by 2
) as u
;
54
INSERT into tempobjektid_T (user, ObjektID, sort)
select "admin", def2, first from temptemp2
;
INSERT
null
Sletter poster hvor der null
ikke er sorteret efter
ny sorterede poster er
indsat.
62
update staggingPD2_T set DATA=Right(DATA, len(DATA)-val(&Param1&)) where
karakteristika="FilKatalog" and Mid(DATA, 2, 1)=":" and LocaltransOK=false;
UPDATE
FjernDrev_Q
Fjerne specifikke drev null
og kataloger for
FilKatalog, bemærk at
det er nødvendigt at
sikre at et : er angivet,
for ikke at ødelægge
katalogangivelser
uden specifik
henvisning til drev.
63
insert into temptemp2 (last) SELECT distinct oo.objekt1
from ObjRelationObj_T as oo, TempObjektID_T
where oo.Objekt2=TempObjektID_T.objektid;
INSERT
LeftObjects
null
64
update TempObjektID_T set sort =&Param1&
UPDATE
SortPrio_Q
indsætter den priorotet Sortering af
der ønskes sesorteret fremviste
efter
data
Ver.:20120808
16
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
65
SELECT Definitionshiraki_T.def1, Definitionshiraki_T.def2, Definitionshiraki_T_1.def2,
SELECT
Definitionshiraki_T_2.def2, Definitionshiraki_T_3.def2, Definitionshiraki_T_4.def2,
Definitionshiraki_T_5.def2, Definitionshiraki_T_6.def2
FROM (((((Definitionshiraki_T LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_1
ON Definitionshiraki_T.def2 = Definitionshiraki_T_1.def1) LEFT JOIN Definitionshiraki_T
AS Definitionshiraki_T_2 ON Definitionshiraki_T_1.def2 = Definitionshiraki_T_2.def1)
LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_3 ON Definitionshiraki_T_2.def2 =
Definitionshiraki_T_3.def1) LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_4 ON
Definitionshiraki_T_3.def2 = Definitionshiraki_T_4.def1) LEFT JOIN Definitionshiraki_T
AS Definitionshiraki_T_5 ON Definitionshiraki_T_4.def2 = Definitionshiraki_T_5.def1)
LEFT JOIN Definitionshiraki_T AS Definitionshiraki_T_6 ON Definitionshiraki_T_5.def2 =
Definitionshiraki_T_6.def1
WHERE (((Definitionshiraki_T.def1)="BasisObj" Or (Definitionshiraki_T.def1)="Karakter"
Or (Definitionshiraki_T.def1)="Relation" Or (Definitionshiraki_T.def1)="Handling"))
ORDER BY Definitionshiraki_T.def1, Definitionshiraki_T.def2, Definitionshiraki_T_1.def2,
Definitionshiraki_T_2.def2, Definitionshiraki_T_3.def2, Definitionshiraki_T_4.def2,
Definitionshiraki_T_5.def2;
DefinitionsHiraki_ Dette view viser op til
Q
7 niveauer i et
definitionshiraki
dannet udfra tabellen
Declare_T. Her med
ODBC syntaks for
outer joins
66
select
Definitionshiraki_ ORACLE syntaks med null
Oracle
outer join tilsvarende
65. Her vist med outer
joins i ORACLE
syntaks, hvorfor en
oracle tabel ed dannet
i SCOTTS skema.
d1.def1,
d1.def2,
d2.def2,
nvl(d3.def2,'-'),
nvl(d4.def2,'-'),
nvl(d5.def2,'-')
from scott.definitionshiraki_T
d1,
(select d2.def1, d2.def2
from scott.definitionshiraki_T d2,
scott.definitionshiraki_T d1
where d1.def1 in ('BasisObj', 'Karakter', 'Relation')
and d2.def1=d1.def2 )
d2,
(select d3.def1, d3.def2
from scott.definitionshiraki_T d3,
scott.definitionshiraki_T d2,
Ver.:20120808
SELECT
17
Report created:
14-01-2015
View der
aktiveres fra
meuen over
hjælpe
dokumenter.
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
SELECT
HovedRapport_Q Optæller antal
fremsøgte poster,
gruperet efter
Benyttet hvor
scott.definitionshiraki_T d1
where d1.def1 in ('BasisObj', 'Karakter', 'Relation')
and d2.def1=d1.def2
and d3.def1=d2.def2)
d3,
(select d4.def1, d4.def2
from scott.definitionshiraki_T d4,
scott.definitionshiraki_T d3,
scott.definitionshiraki_T d2,
scott.definitionshiraki_T d1
where d1.def1 in ('BasisObj', 'Karakter', 'Relation')
and d2.def1=d1.def2
and d3.def1=d2.def2
and d4.def1=d3.def2)
d4,
(select d5.def1, d5.def2
from scott.definitionshiraki_T d5,
scott.definitionshiraki_T d4,
scott.definitionshiraki_T d3,
scott.definitionshiraki_T d2,
scott.definitionshiraki_T d1
where d1.def1 in ('BasisObj', 'Karakter', 'Relation')
and d2.def1=d1.def2
and d3.def1=d2.def2
and d4.def1=d3.def2
and d5.def1=d4.def2)
d5
where d1.def1 in ('BasisObj', 'Karakter', 'Relation')
and d2.def1 (+) =d1.def2
and d3.def1 (+) =d2.def2
and d4.def1 (+) =d3.def2
and d5.def1 (+) =d4.def2
order by 1,2,3;
67
select sort, objektID, count(ObjRelationObj_T.objekt2) as objekt2
from TempObjektID_T, ObjRelationObj_T
where TempObjektID_T.objektid= ObjRelationObj_T.objekt1
Ver.:20120808
18
Report created:
14-01-2015
Bruges som
hovedrapport
af
ID
SQL udtryk
SQL type
Evt. navn
group by sort, objektid
union
select sort, objektID,0
from TempObjektID_T
where not exists (select null from ObjRelationObj_T
where objrelationobj_t.objekt1=TempObjektID_T.objektid)
Forklaring
Benyttet hvor
sorteringskriterier
blandt de fremsøgte
poster i
TempObjektID_T
CrystalRepor
ts
68
DELETE from data_t
WHERE (((Exists (select null from RelationData_T where
data_t.data_id=relationdata_t.data_id))=False));
DELETE
DeleteLonlyData
_Q
Sletter data uden
relationer til objekter
null
69
DELETE from ObjrelationObj_T where objekt1=&Param1& or objekt2=&Param1&;
DELETE
DelOrO_Q
null
SLET Objekt
proceduren
71
SELECT (f2.DATA & f1.DATA) AS Udtryk1
FROM Facttable1_T AS f1, Facttable1_T AS f2
WHERE (((f1.FremsoegtID)=&Param1&) AND ((f1.MedKarakteristika)="Filnavn") AND
((f2.FremsoegtID)=&Param2&) AND ((f2.MedKarakteristika)="FilKatalog"))
and f2.data is not null
UNION
select "NONE" from facttable1_t as f1
where not exists (select null from facttable1_t as f2 where
((f2.FremsoegtID)=&Param1&) AND ((f2.MedKarakteristika)="Filnavn"));
SELECT
null
null
null
73
select max(ID) from Objdeclare_t
SELECT
null
null
null
74
select DATA_ID from DATA_t where Type=&Param2&
SELECT WIEW
and data = (select data from gereba_node1.staggingpd2_t where transactid= &Param1&);
testofdata_q
optæller om data
findes i forvejen
BEMÆRK denne sql
er en pass thrugh,
hvorfor den skal
kvlificeres med
gereba_node1, hvis
den benyttes fra VB
null
Ver.:20120808
19
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
75
SELECT "Indsættelse, redigering og sletning af poster" as grouptext,
sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=10
UNION ALL
SELECT "Import, Export og stagging" as grouptext,
sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=11
UNION ALL
SELECT "Søgninger" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=20
UNION ALL
SELECT "Sikkerhed" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=25
UNION ALL
SELECT "Struktur" as grouptext, sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=30 or functiongroup=31
UNION ALL
SELECT "Handlings objekter" as grouptext,
sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup=40
UNION ALL SELECT "xxxx" as grouptext,
sqlid,sql_exp,type,navn,explain,hvor,functiongroup
from sqlexpresions_t as s
where functiongroup not in (10,11,20,30,31,40) or isnull(functiongroup);
SELECT VIEW
null
Danner rapport over
Bruges til
benyttede SQL udtryk i Access
GEREBA
rapport samt
crystal
Reports.
BEMÆRK:
Uden dobbelt
plinger i
ODBC
databaser
76
INSERT into DATA_T (Type, DATA)
values ( &Param1& , &Param2&) ;
INSERT
null
null
Ver.:20120808
20
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
77
insert into relationdata_T (objekt_ID, data_ID)
values(&Param1&, &Param2&)
INSERT
insertreladata_q
null
null
78
DELETE FROM RelationData_T WHERE objekt_ID=&Param1& ;
DELETE
null
null
null
79
DELETE FROM ObjDeclare_T WHERE ID=&Param1&;
DELETE
null
null
null
80
delete from ObjrelationObj_T where objekt1 in (select TempObjektID_T.ObjektID from
DELETE
tempObjektID_T) or objekt2 in (select TempObjektID_T.ObjektID from tempObjektID_T);
null
null
null
81
DELETE FROM RelationData_T
WHERE objekt_ID in (select TempObjektID_T.ObjektID
from tempObjektID_T) ;
DELETE
null
null
null
82
DELETE FROM ObjDeclare_T
WHERE ID in (select TempObjektID_T.ObjektID
from tempObjektID_T);
DELETE
null
null
null
83
insert into temptemp2 (last, def1, def2, first)
select lastx, typex, firstx, endtype from karakterfirstandlast_q
INSERT
FindKarakter_Q
Finder første og sidste
erklæring efter
KARAKTER, samt om
det er DATA eller
EndDef der afslutter.
Bruges til at
indsætte
EndDef
karakteristka
i DATA_T
tabellen (Kan
ikke køre
som select
into
recordset, fejl
i microsoft?)
Nødvendigt
at indsætte
før den
Ver.:20120808
21
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
faktisk
bruges?
84
SELECT a.def1 as field1, b.def1 as field2 ,a.def2 as field3
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="BasisObj"
and a.def2="EndDef"
union
SELECT a.def1, c.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="BasisObj"
and a.def2="EndDef"
union
SELECT a.def1,d.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="BasisObj"
and a.def2="EndDef"
union
SELECT a.def1,e.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="BasisObj"
and a.def2="EndDef"
UNION SELECT a.def1,f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="BasisObj"
and a.def2="EndDef"
order by 1;
Ver.:20120808
22
SELECT VIEW
Objekt_Q
Finder første erklæring Tester om
efter BasisObj.
def er et
erklæret
objekt (fra
Export?)
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
85
SELECT f1.fremsoegtID,f1.Def, f1.MedKarakteristika,f1.DATA, f2.fremsoegtID, f2.def
FROM FactTable1_T AS f1, FactTable1_T AS f2,TempObjektID_T,ObjrelationObj_t as
oro
WHERE f1.def="Person"
and f1.medKarakteristika in ("User", "Credit")
and f2.fremsoegtID=TempObjektID_T.objektID
and f2.fremsoegtID=oro.objekt1 and oro.relationstype="MedSikkerhed"
and oro.objekt2=f1.fremsoegtID
UNION
SELECT f1.fremsoegtID,f1.Def, f1.MedKarakteristika,f1.DATA, f2.fremsoegtID, f2.def
FROM FactTable1_T AS f1, FactTable1_T AS f2,TempObjektID_T,ObjrelationObj_t as
oro
WHERE f1.def="GroupUnit"
and f1.medKarakteristika in ("ObjGroupUnit", "Demand")
and f2.fremsoegtID=TempObjektID_T.objektID
and f2.fremsoegtID=oro.objekt1 and oro.relationstype="MedSikkerhed"
and oro.objekt2=f1.fremsoegtID
;
SELECT
null
Starten på et
null
sikkerheds view med
USER sammenholdt til
Objekt
87
insert into objrelationobj_T (objekt1, relationstype, objekt2)
values(&Param1&,&Param2&, &Param3& );
INSERT
Addrelation_Q
Insætter relationer
mellem objekter
null
88
update facttable1_t inner join dataregler_t on
dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika
set facttable1_t.prioritet=dataregler_t.prioritet
where dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika
and facttable1_t.def=dataregler_t.objektklasse;
UPDATE
null
Indsætter proritet i
facttable1_t
null
90
UPDATE StaggingPD2_T set LocalTransOK= 1 where tobase=&Param1& and
Action=&Param2&
and LocalTransOK=FALSE
UPDATE
null
Sætter Kun
LOCALTRANS, Ikke
Global
null
91
select * into OrderTable_T from
(SELECT FremsoegtID, Def, MedKarakteristika, data from facttable1_t
INSERT
null
Skal indsætte proc
objekter der benyttes
null
Ver.:20120808
23
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
where def="ProcObj"
and fremsoegtID not in( select fremsoegtID from facttable1_t as f2
where f2.def="ProcObj" and f2.medkarakteristika="Alert")
and medkarakteristika<>"RegistDato")
order by fremsoegtID
;
Forklaring
Benyttet hvor
til data manipulation
(exp-import, editering
mv.) som videregives
som proces ordrer til
den modtagende
gereba base.
92
select fremsoegtid from facttable1_t as f where DATA = &param1&
SELECT
GetIDfromDocum Finder ID til et
null
ent
registreret filnavn, som
forberedelse til objekt
relationer
93
INSERT into TempObjektID_T (user,ObjektID) values (&param2&, &Param1&);
INSERT
null
null
94
INSERT into ObjrelationObj_T (objekt1,relationstype,objekt2)
select &Param1& , &Param2&, objektID from TempObjektID_T
where user =&Param3&
INSERT
null
Indsætter flerværier fra ObjRelation_
temp tabellen til given F
objekt1 ID og med
given relationstype
98
SELECT FromBase, karakteristika, KarakType, DATA, newID, LocalID, transactID
From StaggingPD2_t
Where ToBase=&Param1&
and Action=&Param2&
and GlobalTransOK=FALSE
SELECT
null
Finder samtlige poster null
der deltager fra
Staggnpd2_t
99
UPDATE staggingPD2_T set GlobalTransOK=1
WHERE transactid=&Param1&;
UPDATE
globalok_q
Alle der opfylder
betingelserne sættes
true her
null
102
SELECT x.field1 from Objekt_Q as x
where x.field2="BasisObj"
and x.field1=&Param1&
SELECT
null
Undersøger om
basisobjektet er
erklæret ( se SQL84)
null
Ver.:20120808
24
Report created:
null
14-01-2015
ID
110
SQL udtryk
SELECT "Midtstillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def,
f.medkarakteristika
SQL type
Evt. navn
Forklaring
SELECT
null
Kan finde henvisnings Bruges p.t
objekter med
ikke
karakteristika og
relationer
SELECT
Substitueringstes null
t
FROM TempObjektID_T temp, facttable1_t f
Benyttet hvor
WHERE f.fremsoegtid=temp.objektid
and f.def="ProcObj"
and f.MedKarakteristika="Henvisning"
UNION
SELECT "Venstrestillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def,
f.medkarakteristika
FROM TempObjektID_T temp, facttable1_t f, ObjRelationObj_T oro
WHERE temp.objektid= oro.objekt2
and oro.objekt1=f.fremsoegtid
and f.def="ProcObj"
and f.MedKarakteristika="Henvisning"
UNION
SELECT "Højrestillet henvisning til eksternt objekt", temp.objektid, f.fremsoegtid, f.def,
f.medkarakteristika
FROM TempObjektID_T temp, facttable1_t f, ObjRelationObj_T oro
WHERE temp.objektid= oro.objekt1
and oro.objekt2=f.fremsoegtid
and f.def="ProcObj"
and f.MedKarakteristika="Henvisning;
111
SELECT * from &Param1&
Ver.:20120808
25
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
112
SELECT x.def from Relationer_Q as x
where x.deftype="Relation"
and x.def=&Param1&
SELECT
null
Undersøger om
null
relationen er erklæret
113
SELECT firstx, lastx from KarakterFirstAndLast_Q
where lastx = &Param1&
SELECT
null
Undersøger om
Karakteritika er
erklæret, ogviser om
det er DATA eller
EndDef
null
114
SELECT StaggingPD2_T.Action, StaggingPD2_T.ToBase, StaggingPD2_T.ObjektType,
StaggingPD2_T.Karakteristika, StaggingPD2_T.KarakType, StaggingPD2_T.LocalID,
StaggingPD2_T.NewID, StaggingPD2_T.DATA, StaggingPD2_T.GlobalTransOK
FROM StaggingPD2_T
WHERE StaggingPD2_T.ToBase=&Param1&
AND StaggingPD2_T.GlobalTransOK=False;
SELECT
null
Redigerings
betingelser
null
115
SELECT * from StaggingPD2_T where not exists
(Select null from Objekt_Q as x
where x.b.def1="BasisObj"
and x.a.def1=StaggingPD2_T.objekttype
)
and StaggingPD2_T.ToBase=&Param1&
SELECT
null
Finder ALLE objekter
fra StaggingPD2_T
der skal importeres
som er ugyldige
null
116
UPDATE staggingPD2_T set Action= &Param2&, NewID=&Param1& ,
LocalTransOK=1
where LocalID=&Param1&
UPDATE
null
Udfylder Stagging med null
'yderligere værdier',
skal være sidste
eftersom TranLocal
sættes OK
117
UPDATE staggingPD2_T set Action= "NewObjNewID", NewID=0 where
LocalID=val(&Param1&) and Action="?"
UPDATE
null
Mangel på parametre null
har hindret at Action
ble sat korrekt tidligere
Ver.:20120808
26
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
118
UPDATE staggingPD2_T set Action= "NewObjectWithID", NewID=val(&Param1&) where UPDATE
LocalID=val(&Param1&)
null
UDGÅR,
NewObjectWithID er
ikke en intern
funktion!!!
null
119
INSERT into ObjDeclare_T (def,ID) values (&Param1&, &Param2&);
INSERT
null
null
null
121
UPDATE StaggingPD2_T set NewID=&Param1&
where LocalID=&Param3& and globalTransOK=FALSE
and ToBase=&Param2&
UPDATE
null
null
null
122
SELECT a.def1 as Lastx, b.def1 as typex, a.def2 as firstx, a.def1 as endtype
from definitionshiraki_T as a, definitionshiraki_T as b
where a.def1=b.def2 and b.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1, c.def1,a.def2,c.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1,d.def1,a.def2,d.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1,e.def1,a.def2, e.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Karakter"
and a.def2 in ("EndDef","DATA")
SELECT
KarakterFirstAnd null
Last_Q
null
Ver.:20120808
27
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
UNION SELECT a.def1,f.def1,a.def2,f.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Karakter"
and a.def2 in ("EndDef","DATA");
124
UPDATE staggingPD2_T set Action="ActiveAborted", LocaltransOK=1 where LocalID=0
and NewID=0
and LocalTransOK=FALSE
and ToBase=&Param1&
and FromBase=&Param1&
UPDATE
null
Hvis en post aktivt
null
forkastes f.eks hvis
filanvet eksisterer og
der ikke ønsks
dubletter, skal posten
eksistere i stagging,
men må ikke genediteres
125
INSERT into StaggingPD2_T
(FromBase, Tobase, Action, ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID)
select &Param3&, &Param3&, "NewObjNewID", &Param2&,"RegistDato",
"DATA", str(Now()), &Param1&, &Param1&;
INSERT
null
Indsætter dags dato, null
BEMÆRK det er en
fejl at RegistDato er
hårdtkodet, og at ordre
også må indsættes
hårdtkodet p.g.af
mangel på parametre
(som 40)
126
DELETE from StaggingPD2_T where DATA ="" or isnull(DATA);
DELETE
null
Rydder op i Staggin
tabellen hvad der er
tilovers fra EDIT og
NewID
128
select distinct LocalID,ObjektType,
NewID
From StaggingPD2_T
where GlobaltransOK=FALSE
SELECT
null
Udvidet med felterne null
karakteristika og data,
for at kunne bruges i
PHP (déden
Ver.:20120808
28
Report created:
14-01-2015
null
ID
SQL udtryk
SQL type
Evt. navn
and action =&Param2&
and tobase=&Param1&
Forklaring
Benyttet hvor
redundant at kalde 38)
129
INSERT into StaggingPD2_T
(FromBase, Tobase, Action, ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID,LocalTransOK,GlobalTransOK)
values( &Param2&, &Param2&, "DeleteObject", &Param3&,"-",
"-", "-", &Param1&, &Param1&, 1, FALSE);
INSERT
null
null
130
SELECT FactTable1_T.MedKarakteristika, FactTable1_T.DATA as titel,
FactTable1_T_1.MedKarakteristika, FactTable1_T_1.DATA as Instruk,
FactTable1_T_2.MedKarakteristika, FactTable1_T_2.DATA as Skuespiller,
FactTable1_T_3.MedKarakteristika, FactTable1_T_3.DATA as Beskrivelse
FROM ( ( FactTable1_T LEFT JOIN
(select FremsoegtID, medkarakteristika, data from FactTable1_T as fb where
MedKarakteristika='Instruktør') AS FactTable1_T_1
ON FactTable1_T.FremsoegtID = FactTable1_T_1.FremsoegtID
)
LEFT JOIN
(select FremsoegtID, medkarakteristika, data from FactTable1_T as fc where
MedKarakteristika='Skuespiller') AS FactTable1_T_2
ON FactTable1_T.FremsoegtID = FactTable1_T_2.FremsoegtID
)
LEFT JOIN
(select FremsoegtID, medkarakteristika, data from FactTable1_T as fd where
MedKarakteristika='Beskrivelse') AS FactTable1_T_3
ON FactTable1_T.FremsoegtID = FactTable1_T_3.FremsoegtID
WHERE FactTable1_T.MedKarakteristika='Filmtitel'
SELECT
null
Fremsøger specifikke Benyttes i
felter med givne
JASPER
karakteristika fra
DATAfeltet og
præsenterer dem som
en post. Her med
outer jooin i from
sætningen, til access
og MYSQL?
132
UPDATE staggingPD2_T set GlobalTransOK=1
WHERE NewID=&Param1&
and action=&Param2&
and globalTransOK=0
UPDATE
null
Sætter
GlobalTrans=ok
Ver.:20120808
29
Report created:
null
14-01-2015
fra delete
objekt
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
133
insert into StaggingRelation_T
(action,
frombase,
tobase,
newid1,
localid1,
Relationstype,
localid2,
newid2,
localtransok, globaltransok)
values ( &Param1&,
&Param2&,
&Param3&,
0,
0,
"?",
0,
0,
false, false);
INSERT
null
Indsætter relationer i Fra den
Stagging Relations, se relationsskab
også 287
ende form
134
select * from staggingRelation_T where globaltransOK=false and tobase=&Param1&
SELECT
null
null
null
135
update staggingrelation_t
set NewID1=&Param1&,
LocalID1=&Param2&,
relationstype =&param3&
where localid1=0
and NewID1=0
and relationstype ="?"
and LocalTransOK=FALSE
UPDATE
null
null
null
136
update staggingrelation_t set LocalTransOK=1, globaltransOK=1
where newid1=&Param1& and relationstype=&Param2&
UPDATE
null
null
I selve
CreateRelati
Ver.:20120808
30
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
and newid2=&Param3&;
Benyttet hvor
ons
proceduren
(Snyd!)
140
update StaggingPD2_T set Action=&Param1& where globalTransOK =FALSE
and Action="?"
UPDATE
null
sætter ordren i
stagging tabellen,
eftersom redigerings
formen ikke kender
den
142
INSERT into StaggingPD2_T
(FromBase,
Tobase,
Action,
ObjektType,
Karakteristika,
KarakType,
DATA,
LocalID,
NewID,
LocalTransOK,
GlobalTransOK)
values( &Param1&,
&Param2&,
&Param3&,
"?",
"?",
"?",
"?",
0,
0,
FALSE,
FALSE);
INSERT
null
Generaliseret
null
indsættelse i Stagging,
af DATA hvor
FromBase=Tobase og
LocalID og NewID er
ukendte (0)
Ver.:20120808
31
Report created:
14-01-2015
RedigPrepar
e
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
143
UPDATE StaggingPD2_T
set ObjektType=&Param1&,
Karakteristika=&Param2&,
KarakType=&Param3&
where ObjektType="?"
and Karakteristika="?"
and Karaktype="?"
and LocalTransOK=false
UPDATE
null
null
null
144
UPDATE StaggingPD2_T
set DATA=&Param1&,
LocalID=&Param2&,
NewID=&Param3&,
LocalTransOK=1
where DATA="?"
and LocalID=0
and NewID=0
and LocalTransOK=false
UPDATE
null
Bemærk
LocalTrans=TRUE
null
145
update staggingrelation_t
set LocalID2=&Param1&,
NewID2=&Param2&
where localID2=0
and NewID2=0
and LocalTransOK=FALSE;
UPDATE
null
null
null
147
select * from TempObjektID_T
SELECT
null
null
null
148
insert into TempObjektID_T (user, ObjektID)
values (&Param1&, &Param2&);
INSERT
null
null
null
149
SELECT c.ObjektTypeX
FROM convert_t AS c
SELECT
null
Finder den
konverterede
null
Ver.:20120808
32
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
WHERE c.objektType=&Param1&
and c.databasex=&Param2&
;
Forklaring
Benyttet hvor
objekttype til en
eksportbase
150
SELECT * from facttable1_t where fremsoegtID=&Param1&
and MedKarakteristika<>"Åbn program"
SELECT
null
null
null
152
SELECT *
FROM StaggingPD2_T AS s1
where s1.ToBase=&Param1&
and s1.Action=&Param2&
and s1.TransactID=&Param3&
SELECT
null
null
null
153
select * from DBInstans_t
where navnid=&Param1& and jaspermysqlserver=&Param2&
SELECT
null
Henter link adresser
openreportvi
ew
155
SELECT * from DBInstans_T where visible=1
and lastused=1;
SELECT
null
finder de databaser
null
der skal kunne
vælges, samt den sidt
åbnede, som der
sorteres efter og
derved vises i Form1
156
select * from staggingPD2_T where globaltransOK=FALSE
order by transactID
SELECT
null
null
null
157
INSERT into StaggingPD2_T
(Action,FromBase, ToBase, Karakteristika,
KarakType, DATA, ObjektType,
LocalTransOK, GlobalTransOK,LocalID, NewID)
SELECT "NewObjNewID", &Param1&, &Param2&, f.medkarakteristika, f.definition ,
f.data, f.def, 1, false, t.objektid, 0
FROM facttable1_t AS f, tempobjektid_t AS t
WHERE t.objektid=&Param3&
INSERT
null
Svarer til 19, men
indsætter de værdier
udenom convert_t
null
Ver.:20120808
33
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
null
and t.objektid=f.fremsoegtid
;
158
Delete from FactTable1_t;
DELETE
null
null
159
update staggingRelation_t set NewID2=val(&Param1&)
where RelationsType="ActivateDok"
and tobase=&Param2&
and Action=&Param3&
UPDATE
null
sen opdatering af
ALERT fra
stagging relation,
redigMange_
eftersom
F
redigmange_F poster
slettes ved tidligere
opdatering
160
SELECT * from staggingPD2_T
SELECT
null
null
null
161
update facttable1_t set DATA=replace(DATA, "\", "/")
where medkarakteristika="FilKatalog" or medkarakteristika="Åbn program"
UPDATE
null
Burde gøres i
Jasperreports, men
ODBC problemer
hindrer det i Jasper
SQL
null
162
SELECT a.def1 as ObjType, a.def2 as First, '--' as second, '---' as third,'----' as fourth,
b.def1 as Last,b.def2 as endtype, a.mandatory, b.deftext
from definitionshiraki_T a, definitionshiraki_T b
where a.def1 in ('BasisObj','Karakter','Relation')
and a.def2=b.def1
and b.def2 in ('EndDef', 'DATA')
union
SELECT a.def1 as Type, a.def2 as First, b.def2 as second, '---' as third, '----' as fourth,
c.def1 as Last,c.def2 as endtype, c.mandatory, c.deftext
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1 in ('BasisObj','Karakter','Relation')
and a.def2=b.def1
and b.def2=c.def1
SELECT VIEW
DefinitionHiraki_
Q
View der viser
samtlige poster fra
Definitionshitraki samt
mandatory, vist
hirakisk. Dbinstans_T
viser den aktuelle
base.
Benyttes af
Form1_F(ude
n aktuel
basevisning)
Ver.:20120808
34
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
DELETE
null
Sletter alle mandatory Build
fra hirakiet, som
faccttable og
forberedelse til
Hiraki
indsættelse af nye, og
fjernelse af permanent
slettede. Kører
sammen med 164 og
165
and c.def2 in ('EndDef', 'DATA')
union
SELECT a.def1 as Type, a.def2 as First, b.def2 as second,c.def2 as third, '----' as fourth,
d.def1 as Last,d.def2 as endtype, d.mandatory, d.deftext
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1 in ('BasisObj','Karakter','Relation')
and a.def2=b.def1
and b.def2=c.def1
and c.def2=d.def1
and d.def2 in ('EndDef', 'DATA')
union
SELECT a.def1 as Type, a.def2 as First, b.def2 as second,c.def2 as third,d.def2,
e.def1 as Last,d.def2 as endtype, e.mandatory, e.deftext
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1 in ('BasisObj','Karakter','Relation')
and a.def2=b.def1
and b.def2=c.def1
and c.def2=d.def1
and d.def2=e.def1
and e.def2 in ('EndDef', 'DATA')
order by 1,2
;
163
delete * from DefinitionsHiraki_T where mandatory =1;
Ver.:20120808
35
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
164
delete from DefinitionsHiraki_T as d
where exists (select null from mandatory_t as m where
m.def1=d.def1 and m.def2=d.def2);
DELETE
null
Renser Hiraki for
mandatory (se også
163 og 165)
Erstatter 163???
null
165
insert into DefinitionsHiraki_T (Def1,Def2, mandatory, deftext, DATA_ID)
select def1, def2 , 1, deftext,data_id from mandatory_T;
INSERT
null
Opbygger et samlet
hiraki (163,164,165)
null
166
delete * from temptemp2
DELETE
null
null
null
167
update dbinstans_t set lastused=FALSE
UPDATE
null
Forbereder 168
null
168
update dbinstans_t set lastused=1 where id like '*01'
and gereba_type =&Param1&
and NavnID = &Param2&
UPDATE
null
null
null
169
DELETE from facttable1_t1
DELETE
null
Sletter MYSQL
vebsitets facttabel
null
170
INSERT into facttable1_t1 select * from facttable1_t
INSERT
null
Overfører samtlige
facttable data til
MYSQL website
null
171
DELETE * from dbinstans_t1
DELETE
null
SKAL SLETTES
null
172
INSERT into dbinstans_t1 select * from dbinstans_t;
INSERT
null
SKAL SLETTES
null
173
delete * from staggingpd2local_t
DELETE
null
Skal ændres til at
overføre Mysql til
access
null
Ver.:20120808
36
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
174
insert into staggingpd2_t select FromBase , ToBase ,
Action , ObjektType ,
Karakteristika , KarakType , DATA ,
LocalID, NewID, LocalTransOK, GlobalTransOK
from staggingpd2local_t
INSERT
null
Alt UNDTAGEN
transactid indsættes
null
175
delete * from objrelationobj_t1
DELETE
null
null
null
176
insert into objrelationobj_t1 select * from objrelationobj_t
INSERT
null
null
null
177
Delete * from Definitionshiraki_t1
DELETE
null
null
null
178
INSERT into Definitionshiraki_t1 select * from Definitionshiraki_t
INSERT
null
null
null
179
update staggingrelation_T
inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid1
set staggingrelation_T.NewID1=staggingPD2_T.NewID
where staggingrelation_T.toBase=staggingPD2_T.ToBase
and staggingrelation_T.NewID1=0
and staggingPD2_T.GlobalTransOK=1
and staggingrelation_T.globaltransOK=FALSE
and staggingPD2_T.ToBase=&Param1&
;
UPDATE
null
Hvis objekter er lagt i
staggingpd2_t
samtidig med at de er
lagt i
staggingrelation_t,
kendes ID, som skal
indgå i ralationen
ikke.. Derfor skal der
være sat nogle
temporære ID der
relaterer til stagging_t.
BEMÆRK: disse
sættes ikke fra gereba,
her dannes objekter
altid før de indgår i
relationer, men fra
udefra kommende
Udføres
ummidelbar
før relationer
indsættes.
Ver.:20120808
37
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
import.
Se også 182
180
insert into staggingrelation_t select Action , FromBase ,
ToBase , NewID1,
LocalID1, RelationsType , NewID2 , LocalID2,
LocalTransOK, GlobalTransOK
from staggingrelationlocal_t;
INSERT
null
Alt UNDTAGEN
transactid indsættes
null
181
delete * from staggingrelationlocal_t;
DELETE
null
null
null
182
update staggingrelation_T
inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid2
set staggingrelation_T.NewID2=staggingPD2_T.NewID
where staggingrelation_T.toBase=staggingPD2_T.ToBase
and staggingrelation_T.NewID2=0
and staggingPD2_T.GlobalTransOK=1
and staggingrelation_T.globaltransOK=FALSE
and staggingPD2_T.ToBase=&Param1&
;
UPDATE
null
se 179
null
183
select t.objektid, f.data, f2.data
from facttable1_t as f, facttable1_t as f2, tempobjektid_t as t
where f.fremsoegtid=t.objektid
and f2.fremsoegtid=t.objektid
and f2.medkarakteristika='Filnavn'
and f.medkarakteristika='Åbn program'
and f.def='Dokument'
and f2.def='Dokument';
SELECT
null
null
null
184
update facttable1_t
inner join definitionshiraki_t on definitionshiraki_t.def2 = facttable1_t.medkarakteristika
set medkarakteristika=definitionshiraki_t.def1
where facttable1_t.definition="EndDef";
UPDATE
null
Ved typen EndDef er BuildFacttabl
medkarakteristika=DA e
TA, derfor er
definitionen forud for
Ver.:20120808
38
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
medkarakteristika
mere sigende
185
INSERT into StaggingPD2_T
(Action,FromBase, ToBase, Karakteristika,
KarakType, DATA, ObjektType,
LocalTransOK, LocalID)
SELECT "EditObject" ,&Param2&,&Param3&, f.DATA,
f.definition, f.data, f.def,
false, f.fremsoegtid
FROM facttable1_t AS f
WHERE f.fremsoegtid=&Param1&
and UnionNr<>"U3"
and f.definition="EndDef"
;
INSERT
null
Indsættelse af DATA null
hvor
medkarakteristika=End
Def. Hvis EndDef er
det nødvendigt at
insætte
MedKarakteristika =
DATA, fordi facttablen
nu viser et højere
niveau af enddef
karakteristika. (se 35)
186
insert into StaggingRelation_T
(action,
frombase,
tobase,
newid1,
localid1,
Relationstype,
localid2,
newid2,
localtransok, globaltransok)
select "ExpWithID",
&Param1&,
&Param2&,
&Param3&,
&Param3&,
oro.Relationstype,
oro.objekt2,
oro.objekt2,
INSERT
null
null
Ver.:20120808
39
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
false, false
From ObjRelationObj_T as oro
where oro.objekt1=&Param3&
189
select Newid1, newid2 from staggingrelation_t where localid1=&Param1&
or localid2=&Param1&
SELECT
null
null
null
190
update dbinstans_t1 set lastused=1 where lastused=1
UPDATE
null
Updater på
dbinstans_t for at
udløser trigger en
gang (INSERT udløste
mange) hvor tigger
viderefører til
respektive
gerebabaser.
ved
overførsel til
MYSL test,
hvor tigger
viderefører til
respektive
gerebabaser.
191
select 'Rapport aktiveret' as EVENT,
FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType,
DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID
from staggingpd2_t
where ToBase='xLog' and objektType='LogInfo'
union
select 'Indførsel/redigering af data' as EVENT,
FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType,
DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID
from staggingpd2_t
where objektType <> 'LogInfo'
union
select 'DB overført til server' as EVENT,
FromBase, ToBase,Action,ObjektType,Karakteristika,KarakType,
DATA,LocalID,NewID,LocalTransOK,GlobalTransOK,TransactID
from staggingpd2_t
where ToBase<>'xLog' and objektType='LogInfo'
order by 1,9
SELECT VIEW
null
null
Rapporten
stagging, der
viser log
oplysninger
om
aktiverede
rapporter,
overførsler
melem
Access og
MYSQL,
samt øvrige
redigeringer
Ver.:20120808
40
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
192
CREATE TABLE `convert_t` (
`ObjektType` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT
NULL,
`Field` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL,
`FieldX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT
NULL,
`ObjektTypeX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci
DEFAULT NULL,
`DatabaseX` varchar(50) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT
NULL,
PRIMARY KEY (`Field`,`ObjektType`,`DatabaseX`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
CREATE TABLE
null
MYSQL
null
195
CREATE TABLE `definitionshiraki_t` (
`DATA_ID` int(11) DEFAULT NULL,
`DEF1` varchar(255) NOT NULL,
`DEF2` varchar(255) NOT NULL,
`Mandatory` tinyint(1) NOT NULL DEFAULT FALSE,
`DEFTEXT` text,
PRIMARY KEY (`DEF1`,`DEF2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
MYSQL
null
196
CREATE TABLE `facttable1_t` (
`FREMSOEGTID` int(11) NOT NULL,
`UNIONNR` varchar(10) COLLATE utf8_danish_ci DEFAULT NULL,
`DEF` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`MEDKARAKTERISTIKA` varchar(255) COLLATE utf8_danish_ci NOT NULL,
`DATA` text COLLATE utf8_danish_ci,
`DEFINITION` varchar(255) COLLATE utf8_danish_ci NOT NULL,
`PRIORITET` int(11) DEFAULT NULL,
`KEY` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`KEY`),
KEY `ind_karakteristika` (`MEDKARAKTERISTIKA`)
CREATE TABLE
null
MYSQL, DEFAULT
null
CHARSET=utf8
BEMÆRK det vigtige
index der sikrer view
kan udføres!! SE SQL
311
Ver.:20120808
41
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
) ENGINE=InnoDB AUTO_INCREMENT=4953 DEFAULT CHARSET=utf8
COLLATE=utf8_danish_ci;
197
CREATE TABLE `mandatory_t` (
`DATA_ID` int(11) DEFAULT NULL,
`DEF1` varchar(255) NOT NULL,
`DEF2` varchar(255) NOT NULL,
`DEFTEXT` text,
PRIMARY KEY (`DEF1`,`DEF2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
MYSQL
null
198
CREATE TABLE `objdeclare_t` (
`Def` varchar(100) DEFAULT NULL,
`ID` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE
null
MYSQL
null
199
CREATE TABLE `objrelationobj_t` (
`OBJEKT1` int(11) NOT NULL,
`RELATIONSTYPE` varchar(255) CHARACTER SET latin1 COLLATE latin1_danish_ci
NOT NULL,
`OBJEKT2` int(11) NOT NULL,
PRIMARY KEY (`OBJEKT1`,`RELATIONSTYPE`,`OBJEKT2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE
null
MYSQL
null
200
CREATE TABLE `relationdata_t` (
`Objekt_id` int(11) NOT NULL,
`Data_id` int(11) NOT NULL,
PRIMARY KEY (`Objekt_id`,`Data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
MYSQL
null
201
CREATE TABLE `sqlexpresions_t` (
`SqlID` int(11) NOT NULL,
`SQL_exp` mediumtext CHARACTER SET utf8,
CREATE TABLE
null
MYSQL utf8
null
Ver.:20120808
42
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
`Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`Navn` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`Explain` mediumtext CHARACTER SET utf8,
`Hvor` mediumtext CHARACTER SET utf8,
`FunctionGroup` int(11) DEFAULT NULL,
PRIMARY KEY (`SqlID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
202
CREATE TABLE `staggingpd2local_t` (
`FromBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`ToBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`Action` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`ObjektType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`Karakteristika` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`KarakType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`DATA` text COLLATE utf8_danish_ci,
`LocalID` int(11) DEFAULT NULL,
`NewID` int(11) DEFAULT NULL,
`LocalTransOK` tinyint(1) DEFAULT '0',
`GlobalTransOK` tinyint(1) DEFAULT '0',
`TransactID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`TransactID`)
) ENGINE=InnoDB AUTO_INCREMENT=6388 DEFAULT CHARSET=utf8
COLLATE=utf8_danish_ci
CREATE TABLE
null
MYSQL
null
203
CREATE TABLE `staggingrelation_t` (
`Action` varchar(15) COLLATE latin1_danish_ci DEFAULT NULL,
`FromBase` varchar(100) COLLATE latin1_danish_ci DEFAULT NULL,
`ToBase` varchar(100) COLLATE latin1_danish_ci DEFAULT NULL,
`NewID1` int(11) DEFAULT NULL,
`LocalId1` int(11) DEFAULT NULL,
`RelationsType` varchar(50) COLLATE latin1_danish_ci DEFAULT NULL,
`LocalId2` int(11) DEFAULT NULL,
`NewID2` int(11) DEFAULT NULL,
CREATE TABLE
null
MYSQL
null
Ver.:20120808
43
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
`LocalTransOK` tinyint(1) DEFAULT '0',
`GlobalTransOK` tinyint(1) DEFAULT '0',
`RowID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`RowID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
COLLATE=utf8_danish_ci;
204
CREATE TABLE `tempobjektid_t` (
`USER` varchar(50) NOT NULL,
`OBJEKTID` int(11) NOT NULL,
`SORT` int(11) DEFAULT NULL,
PRIMARY KEY (`OBJEKTID`,`USER`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
MYSQL
null
205
CREATE TABLE `temptemp2` (
`Last` varchar(50) NOT NULL,
`def1` varchar(50) DEFAULT NULL,
`def2` varchar(50) DEFAULT NULL,
`First` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Last`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
MYSQL
null
206
CREATE TABLE `changelog` (
`Dato` varchar(40) NOT NULL,
`Tekst` mediumtext,
`Hovedversion` varchar(100) DEFAULT NULL,
PRIMARY KEY (`Dato`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
null
null
207
CREATE TABLE `staggingpd2_t` (
`FromBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`ToBase` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`Action` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`ObjektType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
CREATE TABLE
null
som staggingpd2_T
null
men beregnet for
COPY og CDAT baser
Ver.:20120808
44
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
`Karakteristika` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`KarakType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`DATA` text COLLATE utf8_danish_ci,
`LocalID` int(11) DEFAULT NULL,
`NewID` int(11) DEFAULT NULL,
`LocalTransOK` tinyint(1) DEFAULT '0',
`GlobalTransOK` tinyint(1) DEFAULT '0',
`TransactID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`TransactID`)
) ENGINE=InnoDB AUTO_INCREMENT=6388 DEFAULT CHARSET=utf8
COLLATE=utf8_danish_ci
208
CREATE TABLE `staggingrelationlocal_t` (
`Action` varchar(15) COLLATE utf8_danish_ci DEFAULT NULL,
`FromBase` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`ToBase` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`NewID1` int(11) DEFAULT NULL,
`LocalId1` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`RelationsType` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`LocalId2` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`NewID2` int(11) DEFAULT NULL,
`LocalTransOK` varchar(1) COLLATE utf8_danish_ci DEFAULT NULL,
`GlobalTransOK` varchar(1) CHARACTER SET latin1 COLLATE latin1_danish_ci
DEFAULT NULL,
`RowID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`RowID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
CREATE TABLE
null
som
null
staggingrelation_T
men beregnet for
COPY og CDAT baser
210
CREATE TABLE `data_t` (
`DATA` mediumtext,
`Type` varchar(100) DEFAULT NULL,
`DATA_ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`DATA_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9633 DEFAULT CHARSET=utf8
CREATE TABLE
null
null
Ver.:20120808
45
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
211
CREATE TABLE `dataregler_t` (
`ObjektKlasse` varchar(100) NOT NULL DEFAULT '',
`TilladtKarakRela` varchar(100) NOT NULL DEFAULT '',
`Obligatorisk` tinyint(1) DEFAULT '0',
`Prioritet` int(11) DEFAULT NULL,
PRIMARY KEY (`ObjektKlasse`,`TilladtKarakRela`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;
CREATE
null
null
null
212
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id as ID,
ucase("U2") as unionnr,
o.def,
data_t.type as medkarakteristika,
data_t.data,
ucase("DATA") as definition
from objdeclare_t o,
data_t,
(select data_id, objekt_id from relationdata_t) ra
where
ra.objekt_id=o.id
and ra.data_id=data_t.data_id
and data_t.data<>data_t.type;
INSERT
null
Opsplittet SQL 28 fordi null
ODBC til MYSQL ikke
virker med UNION
213
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id as ID,
ucase("U1") as unionnr,
o.def,
data_t.type as karakteristika,
data_t.data,
"EndDef" as definition
from objdeclare_t o,
INSERT
null
Opsplittet SQL 28 fordi null
ODBC til MYSQL ikke
virker med UNION
COLLATE=utf8_danish_ci;
Ver.:20120808
46
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
data_t,
(select data_id, objekt_id from relationdata_t) ra
where
ra.objekt_id=o.id
and ra.data_id=data_t.data_id
and data_t.data=data_t.type;
214
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id, "U3",
o.def,
"Åbn program",
Dbinstans_T.BasisArkiv & d2.data & d.data as sti,
"Fremsøgt dok"
from objdeclare_t o, relationData_t rd, relationData_t rd2,
data_t d, data_t d2, DBInstans_t
where
o.id=rd.objekt_id
and o.id=rd2.objekt_id
and o.def="Dokument"
and rd.data_id=d.data_id
and rd2.data_id=d2.data_id
and d.type="Filnavn"
and d2.type="FilKatalog"
and DBInstans_t.lastused=1;
INSERT
null
Opsplittet SQL 28 fordi dan
ODBC til MYSQL ikke facttable1_t
virker med UNION
OMSKREVET til 263
til PHP-MYSQL
216
INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID )
SELECT &Param3&, &Param3&, "?", a.def1,
dr.TilladtKarakRela, x.def2, "", &Param1&, 0
FROM Dataregler_T AS dr, definitionshiraki_T AS a,
definitionshiraki_T AS x
WHERE dr.ObjektKlasse=a.def1
And a.def2="EndDef"
And dr.TilladtKarakRela=x.def1
INSERT
null
opsplittet sql 34
Ver.:20120808
47
Report created:
14-01-2015
null
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
And a.def1=&Param2&;
217
INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID )
SELECT &Param3&, &Param3&, "?", a.def1,
dr.TilladtKarakRela, x.def2, "", &Param1&, 0
FROM Dataregler_T AS dr,
definitionshiraki_t b,
definitionshiraki_T AS a,
definitionshiraki_T AS x
WHERE dr.ObjektKlasse=b.def1
and b.def2=a.def1
And a.def2="EndDef"
And dr.TilladtKarakRela=x.def1
And a.def1=&Param2&;
INSERT
null
null
null
218
INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika,
KarakType, DATA, LocalID, NewID )
SELECT &Param3&, &Param3&, "?", a.def1,
dr.TilladtKarakRela, x.def2, "", &Param1&, 0
FROM Dataregler_T AS dr,
definitionshiraki_t b,
definitionshiraki_T AS a,
definitionshiraki_T c,
definitionshiraki_T AS x
WHERE dr.ObjektKlasse= c.def1
and c.def2=b.def1
and b.def2=a.def1
And a.def2="EndDef"
And dr.TilladtKarakRela=x.def1
And a.def1=&Param2&;
INSERT
null
null
null
219
INSERT INTO StaggingPD2_T ( FromBase, Tobase, action , ObjektType, Karakteristika,
INSERT
null
null
null
Ver.:20120808
48
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
SELECT VIEW
KarakOgRela_Q
null
null
KarakType, DATA, LocalID, NewID )
SELECT &Param3&, &Param3&, "?", a.def1,
dr.TilladtKarakRela, x.def2, "", &Param1&, 0
FROM Dataregler_T AS dr,
definitionshiraki_t b,
definitionshiraki_T AS a,
definitionshiraki_T c,
definitionshiraki_T AS x,
definitionshiraki_T d
where dr.ObjektKlasse= d.def1
and d.def2=c.def1
and c.def2=b.def1
and b.def2=a.def1
and a.def2= "EndDef"
and dr.TilladtKarakRela=x.def1
And a.def1=&Param2&;
220
SELECT a.def1 as field1, b.def1 as field2,b.def2 as field3,a.def2 as field4
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1, c.def1,c.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,d.def1,d.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,e.def1,e.def2,a.def2
Ver.:20120808
49
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
UNION
SELECT a.def1,f.def1,f.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1, b.def1,b.def2, a.def2
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Relation"
and a.def2="EndDef"
union
SELECT a.def1, c.def1,c.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation"
and a.def2="EndDef"
union
SELECT a.def1,d.def1,d.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation"
and a.def2="EndDef"
union
SELECT a.def1,e.def1,e.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Relation"
Ver.:20120808
50
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
SELECT VIEW
Basisarv_Q
null
null
and a.def2="EndDef"
UNION SELECT a.def1,f.def1,f.def2,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Relation"
and a.def2="EndDef";
221
SELECT a.def1, b.def1, b.def2 from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="BasisObj"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1, c.def1, c.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="BasisObj"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,d.def1, d.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="BasisObj"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,e.def1, e.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="BasisObj"
and (a.def2="EndDef" or a.def2="DATA")
UNION SELECT a.def1,f.def1, f.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="BasisObj"
and (a.def2="EndDef" or a.def2="DATA")
ORDER BY 1;
Ver.:20120808
51
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
223
SELECT a.def1 as Lastx, b.def1 as typex, a.def2 as firstx, a.def1 as endtype
from definitionshiraki_T as a, definitionshiraki_T as b
where a.def1=b.def2 and b.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1, c.def1,a.def2,c.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1,d.def1,a.def2,d.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter"
and a.def2 in ("EndDef","DATA")
union
SELECT a.def1,e.def1,a.def2, e.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Karakter"
and a.def2 in ("EndDef","DATA")
UNION SELECT a.def1,f.def1,a.def2,f.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Karakter"
and a.def2 in ("EndDef","DATA")
order by 1;
SELECT VIEW
KarakterFirstand
Last_q
null
null
224
SELECT a.def1 as def, b.def1 as deftype, a.def2 as endtype
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Relation"
SELECT VIEW
Relationer_Q
null
rowsource for
combo2,
relationer på
Ver.:20120808
52
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
and a.def2="EndDef"
union
SELECT a.def1, c.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation"
and a.def2="EndDef"
union
SELECT a.def1, d.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation"
and a.def2="EndDef"
union
SELECT a.def1, e.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Relation"
and a.def2="EndDef"
UNION SELECT a.def1, f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Relation"
and a.def2="EndDef";
225
SELECT a.def1, b.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1, c.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter"
Ver.:20120808
53
Benyttet hvor
soeg_f
SELECT VIEW
Karakteristika_Q
null
Report created:
rowsource på
combo
Karakteristika
få Soeg_F
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
SELECT VIEW
Thesaurus_Q
null
rowsource
combo
thesaurus_fel
t på Soeg_F
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,d.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,e.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
UNION SELECT a.def1,f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Karakter"
and (a.def2="EndDef" or a.def2="DATA")
order by 1;
226
SELECT a.def1, b.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2
and b.def1="Thesaurus"
and a.def2="EndDef"
union
SELECT a.def1, c.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Thesaurus"
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,d.def1,a.def2
Ver.:20120808
54
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Thesaurus"
and a.def2="EndDef"
union
SELECT a.def1,e.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Thesaurus"
and a.def2="EndDef"
UNION SELECT a.def1,f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1="Thesaurus"
and a.def2="EndDef"
order by 1;
227
SELECT DBInstans_T.NavnID FROM DBInstans_T WHERE DBInstans_T.visible=True
and gereba_type='CPROD';
SELECT
null
Implementeret direkte i rowsource for
egenskabsark
valg af
produktions
database
form1
228
select db2.navnid, db2.beskrivelse, db2.gereba_type , db2.dsn
from dbinstans_t as db1,
dbinstans_t as db2
where db1.navnid=db2.navnid
and db1.lastused=1
and db2.gereba_type<>'CPROD';
SELECT
null
null
Ver.:20120808
55
Report created:
combo ved
database
valg <>
produktionsb
ase, men
samme base
navn
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
229
SELECT KarakOgRela_Q.field1, KarakOgRela_Q.field2, KarakOgRela_Q.field3,
KarakOgRela_Q.field4
FROM KarakOgRela_Q
WHERE KarakOgRela_Q.field2="Karakter"
ORDER BY KarakOgRela_Q.field1;
SELECT
null
null
combo på
opdateringskr
av_f
230
INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct
f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4
where
f1.MedKarakteristika Like &Param1&
and f1.definition="DATA"
and f2.MedKarakteristika like &Param4&
and f3.DATA like &Param2&
and f4.Def Like &Param3&
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
and f3.fremsoegtID=f4.fremsoegtID
UNION
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3
where (f1.DATA = &Param1& or f1.data=&Param4&)
and f1.definition="EndDef"
and f2.DATA like &Param2&
and f3.Def Like &Param3&
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
) as u;
INSERT
null
enkelt fritekst, samt
karakteristika
null
231
INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT
ObjDeclare_T.id as id
FROM ObjRelationObj_T, ObjDeclare_T
where (ObjDeclare_T.id=ObjRelationObj_T.objekt1 or ObjDeclare_T.
INSERT
null
ombygget til 292
null
Ver.:20120808
56
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
INSERT
null
Og operator
null
id=ObjRelationObj_T.objekt2)
and ObjRelationObj_T.relationstype= &Param5&
and &Param2&="0"
UNION
SELECT f1.FremsoegtID as id
FROM Facttable1_t as f1
where f1.fremsoegtID=val(&Param6&)
and &Param2& ="0") as u;
232
INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct
f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3, Facttable1_t as f4,
Facttable1_t as f5
where f1.MedKarakteristika Like &Param1&
and f1.definition="DATA"
and f2.MedKarakteristika like &Param4&
and f3.DATA like &Param2& and f5.DATA like &Param5&
and f4.Def Like &Param3&
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
and f3.fremsoegtID=f4.fremsoegtID
and f4.fremsoegtID=f5.fremsoegtID
UNION
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f2, Facttable1_t as f3
where (f1.DATA = &Param1& or f1.data=&Param4&)
and f1.definition="EndDef"
and f2.DATA like &Param2&
and f3.Def Like &Param3&
and f1.FremsoegtID= f2.FremsoegtID
and f2.FremsoegtID= f3.FremsoegtID
) as u;
Ver.:20120808
57
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
235
DELETE * from convert_t1
DELETE
null
null
null
236
insert into convert_t1 select * from convert_t
INSERT
null
null
null
237
select max(data_id) from data_t
SELECT
null
se også ombygget 253 null
PHP
238
select * from temptemp2;
SELECT
null
null
null
239
select data_id from data_t where data=&Param1& and type=&Param1&;
SELECT
null
null
null
240
INSERT into TempObjektID_T (ObjektID,user) select u.id, "xx" from (SELECT distinct
f3.FremsoegtID as id
FROM Facttable1_t as f3, Facttable1_t as f5
where (f3.DATA like &Param3& or f5.DATA like &Param5&)
and f3.FremsoegtID= f5.FremsoegtID
) as u;
INSERT
null
søgning med OR
operator, kun de 2
friteksfelter kan
benyttes
null
241
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id as ID,
"U2" as unionnr,
o.def,
data_t.type as medkarakteristika,
data_t.data,
"DATA" as definition
from objdeclare_t o,
data_t,
(select data_id, objekt_id from relationdata_t) ra
where
o.id=&Param1&
and ra.objekt_id=o.id
INSERT
null
som 212 men med
givent objid
null
Ver.:20120808
58
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
and ra.data_id=data_t.data_id
and data_t.data<>data_t.type;
242
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id as ID,
"U1" as unionnr,
o.def,
data_t.type as karakteristika,
data_t.data,
"EndDef" as definition
from objdeclare_t o,
data_t,
(select data_id, objekt_id from relationdata_t) ra
where
o.id=&Param1&
and ra.objekt_id=o.id
and ra.data_id=data_t.data_id
and data_t.data=data_t.type;
INSERT
null
som 213 med givent
objid
null
243
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id, "U3",
o.def,
"Åbn program",
Dbinstans_T.BasisArkiv & d2.data & d.data as sti,
"Fremsøgt dok"
from objdeclare_t o, relationData_t rd, relationData_t rd2,
data_t d, data_t d2, DBInstans_t
where
o.id=&Param1&
and o.id=rd.objekt_id
and o.id=rd2.objekt_id
and o.def="Dokument"
and rd.data_id=d.data_id
and rd2.data_id=d2.data_id
INSERT
null
som 214 med givent
objid
null
Ver.:20120808
59
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
and d.type="Filnavn"
and d2.type="FilKatalog"
and DBInstans_t.lastused=1;
244
Delete from FactTable1_t where fremsoegtid= &Param1&;
DELETE
null
null
null
245
update facttable1_t set DATA=replace(DATA, "\", "/")
where ( medkarakteristika="FilKatalog" or medkarakteristika="Åbn program") and
fremsoegtid=&param1&
UPDATE
null
null
null
246
update facttable1_t inner join dataregler_t on
dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika
set facttable1_t.prioritet=dataregler_t.prioritet
where dataregler_t.tilladtkarakrela=facttable1_t.medkarakteristika
and fremsoegtid=&Param1&
and facttable1_t.def=dataregler_t.objektklasse;
UPDATE
null
null
null
247
select data from facttable1_t as f, tempobjektid_t as t
where f.fremsoegtid= t.objektid
and medkarakteristika='Åbn program'
and user like &Param1&
SELECT
null
null
null
249
update staggingpd2_t set DATA=replace(DATA, "/", "\")
where globaltransok=0
and (karakteristika="FilKatalog" or karakteristika="Åbn program" );
UPDATE
null
For at sikre
ensartyethed i sti
angivelser, BEMÆRK
dette er hårdtkodet til
et WINDOW filsystem
Fra
RedigMange
_F ved
indsættelse
at
karakteristika
i flere
objekter
250
SELECT a.def1 as field1, b.def1 as field2 ,a.def2 as field3
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1 in ("BasisObj","system")
SELECT
null
Erstatter
Benyttes til
kombinationen af 102 PHP
og 84. tjekker
Ver.:20120808
60
Report created:
14-01-2015
ID
SQL udtryk
SQL type
and a.def2="EndDef"
and a.def1=&Param1&
union
SELECT a.def1, c.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1 in ("BasisObj","system")
and a.def2="EndDef"
and a.def1=&Param1&
union
SELECT a.def1,d.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1 in
("BasisObj","system")
and a.def2="EndDef"
and a.def1=&Param1&
union
SELECT a.def1,e.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1 in ("BasisObj","system")
and a.def2="EndDef"
and a.def1=&Param1&
UNION SELECT a.def1,f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1 in ("BasisObj","system")
and a.def2="EndDef"
and a.def1=&Param1&
order by 1;
Ver.:20120808
61
Evt. navn
Forklaring
existensen af objekter,
i PHP
Report created:
14-01-2015
Benyttet hvor
ID
251
SQL udtryk
SELECT a.def1 as last, b.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Karakter"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1, c.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Karakter"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1,d.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Karakter"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1,e.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Karakter"
and a.def1=&Param1&
and a.def2=&Param2&
UNION SELECT a.def1,f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=&Param1&
and b.def1=c.def2
and c.def1=d.def2
and d.def1=e.def2
Ver.:20120808
62
SQL type
Evt. navn
Forklaring
SELECT
KarakteristikaRel KarakteristikaRela_Q null
a_Q
tjekker existensen af
karakteristika og
relationer, i PHP, samt
at karaktype er korrekt
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
and e.def1=f.def2
and f.def1="Karakter"
and a.def2=&Param2&
union
SELECT a.def1, b.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1="Relation"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1, c.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1="Relation"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1, d.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1="Relation"
and a.def1=&Param1&
and a.def2=&Param2&
union
SELECT a.def1, e.def1, a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1="Relation"
and a.def1=&Param1&
and a.def2=&Param2&
UNION SELECT a.def1, f.def1,a.def2
from definitionshiraki_T a, definitionshiraki_T b, definitionshiraki_T c, definitionshiraki_T
d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.
Ver.:20120808
63
Report created:
14-01-2015
Benyttet hvor
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
def1=f.def2 and f.def1="Relation"
and a.def1=&Param1&
and a.def2=&Param2&
ORDER BY 1;
252
INSERT into ObjDeclare_T (Def, id ) select (&Param1&), (max(id)+1) from ObjDeclare_T; INSERT
null
Erstatter 72 efter
null
sletning af objdeclare ,
local, _t
253
select data_id from data_t where type=&Param1&
and DATA=&Param2&
SELECT
null
237 ombygget til PHP null
254
update staggingrelation_T
inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid1
set staggingrelation_T.NewID1=staggingPD2_T.NewID
where staggingrelation_T.toBase=staggingPD2_T.ToBase
and staggingrelation_T.NewID2=0
and staggingrelation_T.RowID=&Param1&
;
UPDATE
null
erstatter 179 for PHP, null
134 har sikret øvrige
betingelser
255
update staggingrelation_T
inner join staggingPD2_T on staggingPD2_T.localID =staggingrelation_T.localid2
set staggingrelation_T.NewID2=staggingPD2_T.NewID
where staggingrelation_T.toBase=staggingPD2_T.ToBase
and staggingrelation_T.NewID2=0
and staggingrelation_T.RowID=&Param1&
;
UPDATE
null
erstatter 182 for PHP, null
134 har sikret øvrige
betingelser
256
insert into objrelationobj_T (objekt1, relationstype, objekt2)
values(&Param1&,&Param2&, &Param3& )
;
INSERT
null
null
null
257
select Newid1, newid2 from staggingrelation_t where rowid=&Param1& ;
SELECT
null
189 til PHP
null
Ver.:20120808
64
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
258
update staggingrelation_t set LocalTransOK=1, globaltransOK=1
where newid1=&Param1& and relationstype=&Param2&
and newid2=&Param3&;
UPDATE
null
87 ombygget til PHP
null
259
update facttable1_t set DATA=replace(DATA, '\\', '/')
where (medkarakteristika=&Param1& or medkarakteristika=&Param2&);
UPDATE
null
PHP syntaks (161)
null
260
update FactTable1_t set prioritet=99 where (isnull(prioritet) or prioritet=0);
UPDATE
null
null
Buildfacttable
261
INSERT into StaggingPD2_T
(FromBase,
Tobase,
Action,
ObjektType,
Karakteristika,
KarakType,
DATA,
LocalID,
NewID,
LocalTransOK,
GlobalTransOK)
values( &Param1&,
&Param2&,
&Param3&,
&Param4&,
&Param5&,
'?',
"?",
&Param6&,
0,
FALSE,
FALSE);
INSERT
null
142 omskrevet
Mærkelig forhold ved
insættelse af felter
nødvendiggør at
DATA skal tvinges til
UPPER case, for at
testen på data exist
virker
null
Ver.:20120808
65
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
262
UPDATE StaggingPD2_T
set DATA=&Param1&,
karaktype=&Param2&,
newid= &Param4&,
LocalTransOK=true
where localid=&Param3&
and LocalTransOK=false
and DATA='?'
and karaktype='?'
UPDATE
null
143 omskrevet
null
263
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id, ucase("U3"),
o.def,
&Param1&,
concat(Dbinstans_T.BasisArkiv, d2.data, d.data) as sti,
&Param2&
from objdeclare_t o, relationData_t rd, relationData_t rd2,
data_t d, data_t d2, DBInstans_t
where
o.id=rd.objekt_id
and o.id=rd2.objekt_id
and o.def="Dokument"
and rd.data_id=d.data_id
and rd2.data_id=d2.data_id
and d.type="Filnavn"
and d2.type="FilKatalog"
and DBInstans_t.lastused=1;
INSERT
null
se 333 til PHP
multiuser
null
264
update facttable1_t
set medkarakteristika=&Param1&
where medkarakteristika=&Param2&;
UPDATE
null
Updater felt der
fejlagtigt er blevet
lower case felt. (se
263 der er ændret)
null
Ver.:20120808
66
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
265
select distinct objektklasse from dataregler_t
SELECT
null
PHP
null
266
select distinct tilladtkarakrela from dataregler_t where objektklasse=&Param1&;
SELECT
null
PHP
null
267
CREATE TABLE `session_var` (
`session_id` varchar(50) COLLATE utf8_danish_ci NOT NULL,
`user` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`password` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`action` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`frombase` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`tobase` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`php_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`db_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`report_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`db_type` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`debuglevel` int(11) DEFAULT '0',
`param_num1` int(11) DEFAULT '0',
`param_str1` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`param_str2` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`param_str3` varchar(245) COLLATE utf8_danish_ci DEFAULT NULL,
`last_timestamp` int(11) DEFAULT '0',
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
CREATE
null
Bruges af PHP til at
gemme parametre
null
268
delete from gereba_node1.session_var
where session_id= &Param1&;
DELETE
null
Benyttes ved PHP
program.
null
269
insert into gereba_node1.session_var
(session_id, user, password, action, frombase,
tobase, php_server, db_server, db_type, debuglevel)
values (&Param1&,&Param2&, &Param3&, &Param4&,
&Param5&,&Param6&,'xx','xx','yy','00');
INSERT
null
PHP program.
null
Localhost opdateres til
127.0.0.1 i 288.
ebuglevel skal sættes
00 i strengen for at
Ver.:20120808
67
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
køre under php
windows.
270
select * from gereba_node1.session_var as s
where s.session_id=&Param1&;
SELECT
null
null
null
273
update gereba_node1.session_var set param_num1=&Param1&
where session_var.session_id=&Param2&;
UPDATE
null
PHP
null
274
update gereba_node1.session_var set param_str1=&Param1&
where session_var.session_id=&Param2&;
UPDATE
null
PHP
null
275
update gereba_node1.session_var set param_str2=&Param1&
where session_id=&Param2&;
UPDATE
null
null
null
276
INSERT into StaggingPD2_T
(ObjektType,FromBase, Tobase, Action, Karakteristika,
KarakType, DATA, LocalID, NewID, LocalTransOK)
select distinct DEF, &Param2&, &Param2&, &Param3&, &Param4&,
ucase("DATA"),&Param5& ,&Param1&,&Param1&,0
from facttable1_t where fremsoegtid=&Param1&;
INSERT
null
SQL 40 omskrevet til null
PHP, kan sætte hele
posten ind fra facttable
277
update facttable1_t set DATA=concat('http:', DATA)
where medkarakteristika="Åbn program"
and instr(DATA, 'file:')=0;
UPDATE
null
null
278
update facttable1_t set DATA='http:' & DATA
where medkarakteristika="Åbn program" and fremsoegtid=&Param1&;
UPDATE
null
UDGÅR i PHP
null
sammenhæng se 294
279
select localid, newid, action, min(transactid)
from staggingpd2_t
where globaltransok=false
and action like &Param1&;
SELECT
null
finder distincte id og
ordre hvis argument er
%
PHP
Ver.:20120808
68
Report created:
null
14-01-2015
HVOR:
funktion
findaction() i
main
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
280
update staggingpd2_t set LocalTransOK=&Param4&, globaltransOK=&Param5&
where newid like &Param1&
and action like &Param2&
and transactid like &Param3&
;
UPDATE
null
null
null
281
SELECT StaggingPD2_T.Karakteristika, StaggingPD2_T.KarakType,
StaggingPD2_T.LocalID, StaggingPD2_T.NewID, StaggingPD2_T.DATA,
StaggingPD2_T.GlobalTransOK, transactid
FROM StaggingPD2_T
WHERE StaggingPD2_T.newid=&Param1&
AND StaggingPD2_T.GlobalTransOK=False;
SELECT
null
null
null
282
UPDATE StaggingPD2_T set StaggingPD2_T.karakteristika=&Param1& ,
StaggingPD2_T.DATA=&Param2&
where StaggingPD2_T.transactid= &Param3&
AND StaggingPD2_T.GlobalTransOK=False;
UPDATE
null
null
null
283
select * from staggingpd2_t where transactid=&Param1&
SELECT
null
null
null
284
select '1. DATA uden relationer: ' as tekst, data, type, data_id, NULL as objid, null as
Antal from data_t
WHERE not Exists (select null from RelationData_T where
data_t.data_id=relationdata_t.data_id)
UNION
select '2. Objekter uden data: ' as tekst, null, null, null, objdeclare_t.id as objid,null
from objdeclare_t
WHERE not Exists
(select null from RelationData_T where objdeclare_t.id=relationdata_t.objekt_id)
UNION
select '3. Redundante data i data_t: ', data, type, null , null, count(data) from data_t
group by data,type
having count(data)>=2
SELECT
null
Analyse af fejl mellem
data_t, relationdata_t,
objdeclare_t og
objrelationobj_t
Benyttes af
rapport:
gereba_DAT
Acontrol2.jrx
ml
Ver.:20120808
69
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
union
select '4. Relationdata med DATA ikke eksisternde: ',null,
null,relationdata_t.data_id,relationdata_t.objekt_id as objid , null
from relationdata_t
WHERE not Exists
(select null from Data_T where data_id=relationdata_t.data_id)
UNION
select '5. Relationdata med Objekter ikke eksisternede: ',null,null,
relationdata_t.data_id,relationdata_t.objekt_id as objid , null
from relationdata_t
WHERE not Exists
(select null from objdeclare_T where id=relationdata_t.objekt_id)
union
select '6. Relationer mellem objekt(er) der ikke eksisterer: ', null, relationstype,
objekt2,objekt1,null
FROM `objrelationobj_t` where not exists
(select null from objdeclare_t where objrelationobj_t.objekt2=objdeclare_t.id )
or not exists (select null from objdeclare_t where objrelationobj_t.objekt1=objdeclare_t.id
)
order by 1;
285
delete from relationdata_t where objekt_id=0
DELETE
null
Rensning af data,
dette er nr.1n nr 2 er
SQL 68, nr 3: 286
null
286
delete from relationdata_t
WHERE not Exists
(select null from Data_T where data_id=relationdata_t.data_id)
DELETE
null
Rensning af data,
dette er nr.3 nr 2 er
SQL 68, nr 1: 285
null
287
insert into StaggingRelation_T
(action,
frombase,
tobase,
newid1,
SELECT
null
erstatter 133
null
Ver.:20120808
70
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
localid1,
Relationstype,
localid2,
newid2,
localtransok, globaltransok)
values ( &Param1&,
&Param2&,
&Param3&,
&Param4&,
&Param5&,
&Param6&,
0,
0,
false, false);
288
update gereba_node1.session_var set php_server=&Param1&,
db_server=&Param6&,param_num1=&Param2&,db_type=&Param3&,
debuglevel=&Param4&
UPDATE
null
null
null
289
delete from objrelationobj_t where objekt1=&Param1& and relationstype=&Param2& and DELETE
objekt2=&Param3&;
null
PHP
null
290
update gereba_node1.session_var set action=&Param1&
where session_var.session_id=&Param2&;
UPDATE
null
null
null
291
delete from temptemp2;
DELETE
null
null
null
292
INSERT into TempObjektID_T (ObjektID,user)
SELECT distinct FremsoegtID, &Param2&
FROM Facttable1_t
where fremsoegtID in(&Param1&);
INSERT
null
erstatter 231, se ogå
316
null
293
update session_var set last_timestamp= &Param1&
where session_var.session_id=&Param2&;
UPDATE
null
null
null
Ver.:20120808
71
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
294
update facttable1_t set DATA=concat('http:', DATA)
where medkarakteristika=&Param2& and fremsoegtid=&Param1&
and instr(DATA, 'file:')=0;
UPDATE
null
278 omskrevet til PHP null
295
INSERT into TempObjektID_T (ObjektID,user)
SELECT distinct oo.objekt1,"admin"
FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo
WHERE f2.def="ProcObj"
and oo.objekt2=f2.fremsoegtID
and oo.relationstype="ActivateDok"
and f1.fremsoegtID=oo.objekt1
and f2.medkarakteristika="Alert"
and datediff(f2.data, curdate())<=&Param1&
and datediff(f2.data, curdate())>=0
union
SELECT distinct oo.objekt1,"admin"
FROM FactTable1_T AS f1, FactTable1_T AS f2, ObjRelationObj_T AS oo
WHERE f2.def="ProcObj"
and oo.objekt2=f2.fremsoegtID
and oo.relationstype="ActivateDok"
and f1.fremsoegtID=oo.objekt1
and f2.medkarakteristika="Alert"
and datediff(f2.data, curdate())>=&Param1&
and datediff(f2.data, curdate())<=0;
INSERT
null
52 generaliseret for
PHP
null
296
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (SELECT
distinct f2.FremsoegtID as id
FROM Facttable1_t as f2, Facttable1_t as f4
where
f2.definition="DATA"
and f2.MedKarakteristika like &Param1&
and f4.Def Like &Param2&
and f2.FremsoegtID= f4.FremsoegtID
INSERT
null
søgning hvor der ikke
er tastet data (ikke et
stort problem med
ODBC??) ombygget
230
PHP, igen
ombygget
med
sikkerheds
view yil 315
Ver.:20120808
72
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
UNION
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_t as f1, Facttable1_t as f3
where f1.DATA = &Param1&
and f1.definition="EndDef"
and f3.Def Like &Param2&
and f1.FremsoegtID= f3.FremsoegtID
) as u;
297
select count(*) from tempobjektid_t as te
where te.user=&Param1&;
SELECT
null
null
null
298
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param4& from (SELECT
distinct t1.FremsoegtID as id
from FactTable1_T t1,
FactTable1_T t2,
FactTable1_T t3
where t2.data like &Param1&
and t2.def like &Param3&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.medkarakteristika like &Param2&
and t3.definition='DATA'
union
select distinct t1.FremsoegtID
from FactTable1_T t1,
FactTable1_T t2,
FactTable1_T t3
where t2.data like &Param1&
and t2.def like &Param3&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.data like &Param2&
and t3.definition='EndDef') as u;
INSERT
null
Hvis fritekst IKKE er
tom (PHP) og def
ubestemt. Erstatter
230, se 317
null
Ver.:20120808
73
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
299
insert into staggingpd2local_t select * from staggingpd2_t where globaltransok=0;
INSERT
null
null
null
300
insert into staggingrelationlocal_t select * from staggingrelation_t where globaltransok=0; INSERT
null
null
null
301
delete from TempObjektID_T where user like &Param1&;
DELETE
null
SQL 8 omskrevet med null
session_id til PHP
302
delete from gereba_node1.session_var;
DELETE
null
null
null
303
update gereba_node1.session_var set param_str3=&Param1&
where session_var.session_id=&Param2&;
UPDATE
null
null
null
304
delete from gereba_node1.tempobjektid_t where user
not in (select distinct session_id from gereba_node1.session_var);
DELETE
null
Oprydning i multiuser
modellen
null
305
delete FROM gereba_node1.session_var
where &Param1& - last_timestamp > &Param2&;
DELETE
null
Oprydning i multiuser
modellen
null
306
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (
SELECT distinct t1.FremsoegtID as id
from FactTable1_T t1,
FactTable1_T t2,
FactTable1_T t3
where t2.data like &Param1&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.data like &Param2&
) as u;
INSERT
null
søge sql med AND
operator, se 318
null
307
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (
SELECT distinct t1.FremsoegtID as id
from FactTable1_T t1
INSERT
null
søge sql med OR
operator
null
Ver.:20120808
74
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
where t1.data like &Param1&
or (t1.data like &Param2&) order by 1
) as u;
308
update gereba_node1.session_var set report_server=&Param1&
where session_var.session_id=&Param2&;
UPDATE
null
null
null
309
CREATE TABLE `userpasswcredit_t` (
`base` varchar(45) COLLATE utf8_danish_ci NOT NULL,
`username` varchar(45) COLLATE utf8_danish_ci NOT NULL DEFAULT 'nn',
`password` varchar(100) COLLATE utf8_danish_ci NOT NULL,
`credit` int(11) DEFAULT '0',
`name` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`comment` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
PRIMARY KEY (`base`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE
null
null
null
310
set @basename=&Param1&; set @username=&Param2&; set @credit=&Param3&;
SET
null
Bruges til fine grain
null
(single row security)
på MYSQL base
benyttet af PHP
sammen med view og
funktioner. MEN ikke
som denne SQL, men
direktei PHP
311
create index ind_karakteristika on facttable1_t (medkarakteristika);
CREATE INDEX
null
Danner index på
null
fattable1_t til brug ved
fine grain view, vigtig
tilføjelse til SQL 196
312
select * from gereba_node1.userpasswcredit_t
where BINARY username=&Param1& and BINARY password =&Param2&
and &Param3& like base;
SELECT
null
null
Ver.:20120808
75
Report created:
null
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
CREATE VIEW
null
Her eksemplificeret på null
pim
314
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
CREATE VIEW
SQL SECURITY DEFINER VIEW `objrelationobj_v` AS
select distinct `ob`.`OBJEKT1` AS `objekt1`,`ob`.`RELATIONSTYPE` AS `relationstype`,
`ob`.`OBJEKT2` AS `objekt2`
from (`objrelationobj_t` `ob` join `facttable1_t` `fa`)
where ((`ob`.`OBJEKT2` = `fa`.`FREMSOEGTID`) and (`fa`.`MEDKARAKTERISTIKA` =
'Credit')
and (`fa`.`DATA` <= `pim`.`getcredit`()))
union
select distinct `ob`.`OBJEKT1` AS `objekt1`,
`ob`.`RELATIONSTYPE` AS `relationstype`,`ob`.`OBJEKT2` AS `objekt2`
from (`objrelationobj_t` `ob` join `facttable1_t` `fa`)
where ((`ob`.`OBJEKT1` = `fa`.`FREMSOEGTID`)
and (`fa`.`MEDKARAKTERISTIKA` = 'Credit')
and (`fa`.`DATA` <= `pim`.`getcredit`()))
null
BRUGES IKKE MERE null
?
Her kun vist for pim
basen, skal dannes på
samtlige shemas.
315
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (SELECT
null
Ombygget 296 med
313
CREATE OR REPLACE VIEW facttable1_v AS
select *
from pim.facttable1_t as fa
Benyttet hvor
and fa.medkarakteristika='credit'
and fa.data <=&Param1&
union
select *
from pim.facttable1_t as fa
where fremsoegtid not in
(select fremsoegtid from pim.facttable1_t as fa2
where fa2.medkarakteristika='credit')
;
Ver.:20120808
76
INSERT
Report created:
14-01-2015
null
ID
SQL udtryk
SQL type
Evt. navn
distinct f2.FremsoegtID as id
FROM Facttable1_v as f2, Facttable1_v as f4
where
f2.definition="DATA"
and f2.MedKarakteristika like &Param1&
and f4.Def Like &Param2&
and f2.FremsoegtID= f4.FremsoegtID
UNION
SELECT distinct f1.FremsoegtID as id
FROM Facttable1_v as f1, Facttable1_v as f3
where f1.DATA = &Param1&
and f1.definition="EndDef"
and f3.Def Like &Param2&
and f1.FremsoegtID= f3.FremsoegtID
) as u;
Forklaring
Benyttet hvor
sikkerheds view
316
INSERT into TempObjektID_T (ObjektID,user)
SELECT distinct FremsoegtID, &Param2&
FROM Facttable1_v
where fremsoegtID in(&Param1&);
INSERT
null
se 292
null
317
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param4& from (SELECT
distinct t1.FremsoegtID as id
from FactTable1_v t1,
FactTable1_v t2,
FactTable1_v t3
where t2.data like &Param1&
and t2.def like &Param3&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.medkarakteristika like &Param2&
and t3.definition='DATA'
union
select distinct t1.FremsoegtID
INSERT
null
se 298
null
Ver.:20120808
77
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
from FactTable1_v t1,
FactTable1_v t2,
FactTable1_v t3
where t2.data like &Param1&
and t2.def like &Param3&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.data like &Param2&
and t3.definition='EndDef') as u;
318
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (
SELECT distinct t1.FremsoegtID as id
from FactTable1_v t1,
FactTable1_v t2,
FactTable1_v t3
where t2.data like &Param1&
and t1.FremsoegtID=t2.Fremsoegtid
and t2.Fremsoegtid=t3.Fremsoegtid
and t3.data like &Param2&
) as u;
INSERT
null
se 306
null
319
INSERT into TempObjektID_T (ObjektID,user) select u.id, &Param3& from (
SELECT distinct t1.FremsoegtID as id
from FactTable1_T t1
where t1.data like &Param1&
or (t1.data like &Param2&) order by 1
) as u;
INSERT
null
se 307
null
320
select 'connection_id: ',connection_id(),
' fkt.getcredit: ', getcredit();
SELECT
null
Test af mysql
connection_id og
funktion
null
321
select getcredit() ;
SELECT
null
null
null
Ver.:20120808
78
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
322
insert into gereba_node1.temprelaobj_t (sessionid, obj1,relation,obj2)
select &Param1&, objekt1, relationstype,objekt2
from objrelationobj_t
where objekt1 in (select objektid from gereba_node1.tempobjektid_t)
or objekt2 in (select objektid from gereba_node1.tempobjektid_t)
INSERT
null
UDGÅR til fordel for
329
null
323
delete from gereba_node1.temprelaobj_t where sessionid like &Param1&
DELETE
null
like operator for at
kunne slette alt fra
tabellen med %
null
324
CREATE TABLE `temprelaobj_t` (
`sessionid` varchar(60) COLLATE utf8_danish_ci NOT NULL,
`obj1` int(11) NOT NULL,
`relation` varchar(45) COLLATE utf8_danish_ci NOT NULL,
`obj2` int(11) NOT NULL,
PRIMARY KEY (`obj1`,`relation`,`obj2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
CREATE TABLE
null
null
null
325
select count(9) FROM gereba_node1.temprelaobj_t where sessionid =&Param1&;
SELECT
null
null
null
326
insert into gereba_node1.tempobjektid_t (objektid, user) select distinct obj2, sessionid
from gereba_node1.temprelaobj_t where sessionid=&Param1&;
INSERT
null
obj kan erstattes
Venstrestillet,
afParm her eller i 327 højrestillet
327
insert into gereba_node1.tempobjektid_t (objektid, user) select distinct obj1, sessionid
from gereba_node1.temprelaobj_t where sessionid=&Param1&;
INSERT
null
obj kan erstattes
Venstrestillet,
afParm her eller i 326 højrestillet
328
delete from gereba_node1.tempobjektid_t
where objektid not in (select fremsoegtid from facttable1_v)
and user = &Param1&;
DELETE
null
null
null
329
insert into gereba_node1.temprelaobj_t ( obj1, relation, obj2, sessionid)
select ob.OBJEKT1 AS obj1,ob.RELATIONSTYPE AS relation,
ob.OBJEKT2 AS obj2,t.user as sessionid
INSERT
null
null
null
Ver.:20120808
79
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
create table
null
194 bør slettes
null
eftersom triggeren ikke
mere benyttes
from objrelationobj_t as ob, gereba_node1.tempobjektid_t as t
where t.user=&Param1&
and ob.OBJEKT1 =t.objektid
union
select ob.OBJEKT1 AS obj1,ob.RELATIONSTYPE AS relation,
ob.OBJEKT2 AS obj2,t.user as sessionid
from objrelationobj_t as ob, gereba_node1.tempobjektid_t as t
where t.user=&Param1&
and ob.OBJEKT2 =t.objektid
;
330
CREATE TABLE `dbinstans_t` (
`StiOgNavn` varchar(250) COLLATE utf8_danish_ci DEFAULT NULL,
`Dato` varchar(40) COLLATE utf8_danish_ci DEFAULT NULL,
`Beskrivelse` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`NavnID` varchar(50) COLLATE utf8_danish_ci DEFAULT '',
`visible` tinyint(1) DEFAULT '0',
`LastUsed` tinyint(1) DEFAULT '0',
`AutoOpretDato` tinyint(1) DEFAULT '1',
`BasisArkiv` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`tema_katalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`gereba_type` varchar(255) COLLATE utf8_danish_ci DEFAULT '?',
`SpecialForms` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`DocumentCatalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`DefaultUser` varchar(20) COLLATE utf8_danish_ci DEFAULT NULL,
`UserValue` int(11) DEFAULT NULL,
`BenyttesCrystal` bit(1) DEFAULT NULL,
`JasperMainReport` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`JasperCatalog` varchar(255) COLLATE utf8_danish_ci DEFAULT NULL,
`JasperMYSQLserver` varchar(100) COLLATE utf8_danish_ci DEFAULT '',
`Comment` mediumtext COLLATE utf8_danish_ci,
`conector` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`conuserpasw` varchar(80) COLLATE utf8_danish_ci DEFAULT NULL,
`driver` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
Ver.:20120808
80
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
`dsn` varchar(50) COLLATE utf8_danish_ci DEFAULT NULL,
`port` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`id` varchar(45) COLLATE utf8_danish_ci NOT NULL DEFAULT '',
`visnings_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`database_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
`program_server` varchar(100) COLLATE utf8_danish_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
331
delete from gereba_node1.temprelaobj_t
where temprelaobj_t.sessionid=&Param1&
and (temprelaobj_t.obj1 in ( select fa.fremsoegtid from facttable1_t fa where
`fa`.`MEDKARAKTERISTIKA` ="Credit" and `fa`.`DATA` > `pim`.`getcredit`()))
or (temprelaobj_t.obj2 in ( select fa.fremsoegtid from facttable1_t fa where
`fa`.`MEDKARAKTERISTIKA` = "Credit" and `fa`.`DATA` > `pim`.`getcredit`()));
DELETE
null
null
null
332
select * from gereba_node1.dbinstans_t where id =&Param1&
SELECT
null
null
null
333
insert into facttable1_t (fremsoegtid, unionnr, def, medkarakteristika, data, definition)
select o.id, ucase("U3"),
o.def,
&Param1&,
concat(Dbinstans_T.BasisArkiv, d2.data, d.data) as sti,
&Param2&
from objdeclare_t o, relationData_t rd, relationData_t rd2,
data_t d, data_t d2, DBInstans_t
where
o.id=rd.objekt_id
and o.id=rd2.objekt_id
and o.def="Dokument"
and rd.data_id=d.data_id
and rd2.data_id=d2.data_id
and d.type="Filnavn"
INSERT
null
263 ændret til PHP
multiuser
null
Ver.:20120808
81
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
and d2.type="FilKatalog"
and DBInstans_t.id= &Param3&;
334
select ObjektType,
Karakteristika, KarakType, DATA,
NewID, LocalID, transactID
From StaggingPD2_T
where GlobaltransOK=FALSE
and tobase=&Param1&
and action =&Param2&
and LocaltransOK=&Param3&
;
SELECT
null
Udvidelse af SQL 38 til null
PHP
335
select ObjektType,
Karakteristika, KarakType, DATA,
NewID, LocalID, transactID
From StaggingPD2_T
where GlobaltransOK=FALSE
and tobase=&Param1&
and action =&Param2&
and LocaltransOK=&Param3&
and newid=&Param4&
;
SELECT
null
som 334, men
null
beregnet til
indsættelse af data_id
med specifikt newid
336
select distinct LocalID,ObjektType,
NewID
From StaggingPD2_T
where GlobaltransOK=FALSE
and tobase=&Param1&
and action =&Param2&
and localid=&Param3&
;
SELECT
null
null
Ver.:20120808
82
Report created:
datainsert
PHP
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
337
UPDATE staggingPD2_T set Action= &Param2&, NewID=&Param1& ,
LocalTransOK=0
where LocalID=&Param1&
UPDATE
null
116 ombygget til at
kunne sætte
localtrans=FALSE
null
338
SELECT a.def1, b.def1, b.def2 from definitionshiraki_T a, definitionshiraki_T b
where a.def1=b.def2 and b.def1 in("BasisObj","system")
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1, c.def1, c.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c
where a.def1=b.def2 and b.def1=c.def2 and c.def1 in("BasisObj","system")
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,d.def1, d.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1
in("BasisObj","system")
and (a.def2="EndDef" or a.def2="DATA")
union
SELECT a.def1,e.def1, e.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and e.def1
in("BasisObj","system")
and (a.def2="EndDef" or a.def2="DATA")
UNION SELECT a.def1,f.def1, f.def2 from definitionshiraki_T a, definitionshiraki_T b,
definitionshiraki_T c, definitionshiraki_T d, definitionshiraki_T e, definitionshiraki_T f
where a.def1=b.def2 and b.def1=c.def2 and c.def1=d.def2 and d.def1=e.def2 and
e.def1=f.def2 and f.def1 in("BasisObj","system")
and (a.def2="EndDef" or a.def2="DATA")
ORDER BY 2,1;
SELECT
null
null
null
339
select f1.fremsoegtid, f1.medkarakteristika as tekst,
f2.medkarakteristika as jrxml,f1.data as dtekst,f2.data as djrxml
SELECT
null
null
null
Ver.:20120808
83
Report created:
14-01-2015
ID
SQL udtryk
SQL type
Evt. navn
Forklaring
Benyttet hvor
from facttable1_t as f1,facttable1_t as f2
where f1.medkarakteristika in('rapport_tekst')
and f2.medkarakteristika in('rapport_jrxml')
and f1.fremsoegtid=f2.fremsoegtid
340
delete from gereba_node1.staggingpd2_t where (localtransok=0 or globaltransok=0)
DELETE
null
null
null
341
select count(*) from gereba_node1.staggingpd2_t where (localtransok=0 or
globaltransok=0)
SELECT
null
null
null
342
delete from gereba_node1.staggingrelation_t where (localtransok=0 or globaltransok=0)
DELETE
null
null
null
Total number of SQL expressions
Ver.:20120808
84
272
Report created:
14-01-2015