|
17.08.2017, 11:00
#39506705
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Сделал так: В ms sql в бд где храняться таблицы на каждый тип объекта:
snp_var,snp_pop,snp_trt,snp_model и т.д. создал представление.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
CREATE VIEW V_ODI_OTCHET_OB_IZMENENIYAH
(TYPE_OBJ ,
NAME_OBJ ,
DATA_CHANGE ,
[USER],
VERSION,
DATA_FORM_VERSION ) AS
SELECT 'ИНТЕРФЕЙС' AS 'TYPE_OBJ',
INTERFEIS.POP_NAME AS 'NAME_OBJ',
INTERFEIS.LAST_DATE AS 'DATA_CHANGE',
INTERFEIS.LAST_USER AS 'USER',
INTERFEIS.EXT_VERSION AS 'VERSION',
VERSIA.LAST_DATE as 'DATA_FORM_VERSION'
FROM ODI_MASTER.DBO.SNP_POP INTERFEIS left join ODI_MASTER.dbo.SNP_VERSION VERSIA
on INTERFEIS.I_POP = VERSIA.I_INSTANCE AND VERSIA.I_OBJECTS=3100
WHERE CONVERT(DATE,INTERFEIS.LAST_DATE)=CONVERT (DATE, GETDATE())
AND (VERSIA.LAST_DATE=(SELECT MAX(v2.LAST_DATE)
FROM ODI_MASTER.dbo.SNP_VERSION v2 WHERE VERSIA.I_INSTANCE=V2.I_INSTANCE)
OR VERSIA.LAST_DATE IS NULL)
UNION
SELECT 'ПАКЕТ',
PAKET.PACK_NAME,
PAKET.LAST_DATE,
PAKET.LAST_USER,
PAKET.EXT_VERSION AS 'VERSION',
VERSIA.LAST_DATE
FROM ODI_MASTER.DBO.SNP_PACKAGE PAKET left join ODI_MASTER.dbo.SNP_VERSION VERSIA
on PAKET.I_PACKAGE=VERSIA.I_INSTANCE AND VERSIA.I_OBJECTS=3200
WHERE CONVERT(DATE,PAKET.LAST_DATE)=CONVERT (DATE, GETDATE())
AND (VERSIA.LAST_DATE=(SELECT MAX(v2.LAST_DATE)
FROM ODI_MASTER.dbo.SNP_VERSION v2 WHERE VERSIA.I_INSTANCE=V2.I_INSTANCE)
OR VERSIA.LAST_DATE IS NULL)
UNION
SELECT 'ПЕРЕМЕННЫЕ',
PEREMEN.VAR_NAME,
PEREMEN.LAST_DATE,
PEREMEN.LAST_USER,
PEREMEN.EXT_VERSION AS 'VERSION',
VERSIA.LAST_DATE
FROM ODI_MASTER.DBO.SNP_VAR PEREMEN left join ODI_MASTER.dbo.SNP_VERSION VERSIA
on PEREMEN.I_VAR=VERSIA.I_INSTANCE AND VERSIA.I_OBJECTS=3500
WHERE CONVERT(DATE,PEREMEN.LAST_DATE)=CONVERT (DATE, GETDATE())
AND (VERSIA.LAST_DATE=(SELECT MAX(v2.LAST_DATE)
FROM ODI_MASTER.dbo.SNP_VERSION v2 WHERE VERSIA.I_INSTANCE=V2.I_INSTANCE)
OR VERSIA.LAST_DATE IS NULL)
UNION
SELECT CASE WHEN TRT_TYPE='U' THEN 'ПРОЦЕДУРА' ELSE 'KNOWLEDGE MODULES' END,
PROCEDUR.TRT_NAME ,
PROCEDUR.LAST_DATE,
PROCEDUR.LAST_USER,
PROCEDUR.EXT_VERSION AS 'VERSION',
VERSIA.LAST_DATE
FROM ODI_MASTER.DBO.SNP_TRT PROCEDUR left join ODI_MASTER.dbo.SNP_VERSION VERSIA
on PROCEDUR.I_TRT=VERSIA.I_INSTANCE AND VERSIA.I_OBJECTS IN (3600)
WHERE CONVERT(DATE,PROCEDUR.LAST_DATE)=CONVERT (DATE, GETDATE())
AND (VERSIA.LAST_DATE=(SELECT MAX(v2.LAST_DATE)
FROM ODI_MASTER.dbo.SNP_VERSION v2 WHERE VERSIA.I_INSTANCE=V2.I_INSTANCE)
OR VERSIA.LAST_DATE IS NULL)
|
|
|
