Сафронов А.И.
Москва – 2016 г.
- Главная
- Разное
- Дизайн
- Бизнес и предпринимательство
- Аналитика
- Образование
- Развлечения
- Красота и здоровье
- Финансы
- Государство
- Путешествия
- Спорт
- Недвижимость
- Армия
- Графика
- Культурология
- Еда и кулинария
- Лингвистика
- Английский язык
- Астрономия
- Алгебра
- Биология
- География
- Детские презентации
- Информатика
- История
- Литература
- Маркетинг
- Математика
- Медицина
- Менеджмент
- Музыка
- МХК
- Немецкий язык
- ОБЖ
- Обществознание
- Окружающий мир
- Педагогика
- Русский язык
- Технология
- Физика
- Философия
- Химия
- Шаблоны, картинки для презентаций
- Экология
- Экономика
- Юриспруденция
Информационное обеспечение систем управления. Лабораторные работы. Раздел 5 презентация
Содержание
- 1. Информационное обеспечение систем управления. Лабораторные работы. Раздел 5
- 2. Готовые запросы Запрос 5 – Общий SELECT
- 3. Готовые запросы Запрос 5 – Вариант №1
- 4. Готовые запросы Запрос 5 – Вариант №2
- 5. Готовые запросы Запрос 5 – Вариант №3
- 6. Готовые запросы Запрос 5 – Вариант №4
- 7. Готовые запросы Запрос 5 – Вариант №5
- 8. Готовые запросы Запрос 5 – Вариант №6
- 9. Готовые запросы Запрос 5 – Вариант №7
- 10. Готовые запросы Запрос 5 – Вариант №8
- 11. Готовые запросы Запрос 5 – Вариант №9
- 12. Готовые запросы Запрос 5 – Вариант №10
- 13. Готовые запросы Запрос 5 – Вариант №11
- 14. Готовые запросы Запрос 5 – Вариант №12
- 15. Готовые запросы Запрос 5 – Вариант №13
- 16. Готовые запросы Запрос 5 – Вариант №14
- 17. Готовые запросы Запрос 5 – Вариант №15
Готовые запросы Запрос 5 – Общий SELECT $VAR$ AS VAR_ID, $UOL$ AS ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN, CASE WHEN UL.PRC IS NULL THEN '0' ELSE UL.PRC END AS PRC, S.FULL_NAME||'('||S.STVN||'
Слайд 1Информационное обеспечение систем управления
Кафедра «Управление и защита информации»
Учебная дисциплина:
Лабораторные работы. Раздел
5
Сафронов А.И.
Сафронов А.И.
Слайд 2Готовые запросы
Запрос 5 – Общий
SELECT $VAR$ AS VAR_ID, $UOL$ AS ID_UOL,
UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = $UOL$ AND VAR_ID = $VAR$) AND UL.VAR_ID = $VAR$
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = $UOL$ AND VAR_ID = $VAR$) AND UL.VAR_ID = $VAR$
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 3Готовые запросы
Запрос 5 – Вариант №1
SELECT $VAR$ AS VAR_ID, $UOL$ AS
ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5106 AND VAR_ID = 2014122418204663) AND UL.VAR_ID = 2014122418204663
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5106 AND VAR_ID = 2014122418204663) AND UL.VAR_ID = 2014122418204663
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 4Готовые запросы
Запрос 5 – Вариант №2
SELECT $VAR$ AS VAR_ID, $UOL$ AS
ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5104 AND VAR_ID = 2015011310571472) AND UL.VAR_ID = 2015011310571472
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5104 AND VAR_ID = 2015011310571472) AND UL.VAR_ID = 2015011310571472
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 5Готовые запросы
Запрос 5 – Вариант №3
SELECT $UOL$ AS ID_UOL, UL.ID_PREDPR, S.OBJECT_ID,
S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 6Готовые запросы
Запрос 5 – Вариант №4
SELECT $VAR$ AS VAR_ID, $UOL$ AS
ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 7Готовые запросы
Запрос 5 – Вариант №5
SELECT $VAR$ AS VAR_ID, $UOL$ AS
ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5125 AND VAR_ID = 2016021009054736) AND UL.VAR_ID = 2016021009054736
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 5125 AND VAR_ID = 2016021009054736) AND UL.VAR_ID = 2016021009054736
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 8Готовые запросы
Запрос 5 – Вариант №6
SELECT $UOL$ AS ID_UOL,
CASE WHEN
UL.ID_PREDPR IS NULL THEN ‘9999'
ELSE UL.ID_PREDPR END AS PR_ED_PR,
S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN ‘9999'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
ELSE UL.ID_PREDPR END AS PR_ED_PR,
S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN ‘9999'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 9Готовые запросы
Запрос 5 – Вариант №7
SELECT 2014031115053854 AS ID_URLB, UL.ID_PREDPR, S.OBJECT_ID,
S.STVN,
CASE WHEN UL.PRC IS NULL THEN ‘0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_URLB = 2014031115053854 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN ‘0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_URLB = 2014031115053854 )
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 10Готовые запросы
Запрос 5 – Вариант №8
SELECT $UOL$ AS ID_UOL, UL.ID_PREDPR, S.OBJECT_ID,
S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 ) AND NOT UL.ID_PREDPR IS NULL
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 6304 ) AND NOT UL.ID_PREDPR IS NULL
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 11Готовые запросы
Запрос 5 – Вариант №9
SELECT $UOL$ AS ID_UOL, UL.ID_PREDPR, S.OBJECT_ID,
S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 9633 AND VAR_ID = 2014122005015204) OR UL.VAR_ID = 2016012701124690
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 9633 AND VAR_ID = 2014122005015204) OR UL.VAR_ID = 2016012701124690
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 12Готовые запросы
Запрос 5 – Вариант №10
SELECT UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN
UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 2014122418204663)
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 2014122418204663)
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 13Готовые запросы
Запрос 5 – Вариант №11
SELECT UP.*, S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME,
S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 9610 AND VAR_ID = 962014) AND UL.VAR_ID = 962014
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 9610 AND VAR_ID = 962014) AND UL.VAR_ID = 962014
Слайд 14Готовые запросы
Запрос 5 – Вариант №12
SELECT UP.VAR_ID, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE
WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE NOT OP_ST_1_F IS NULL) AND S.STVN > 3
GROUP BY UP.VAR_ID, UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE NOT OP_ST_1_F IS NULL) AND S.STVN > 3
GROUP BY UP.VAR_ID, UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 15Готовые запросы
Запрос 5 – Вариант №13
SELECT $VAR$ AS VAR_ID, $UOL$ AS
ID_UOL, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 103 AND VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE ID_UOL = 103 AND VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 16Готовые запросы
Запрос 5 – Вариант №14
SELECT $VAR$ AS VAR_ID, UL.ID_PREDPR, S.OBJECT_ID,
S.STVN,
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
CASE WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE VAR_ID = 12014) AND UL.VAR_ID = 12014
GROUP BY UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
Слайд 17Готовые запросы
Запрос 5 – Вариант №15
SELECT UP.VAR_ID, UL.ID_PREDPR, S.OBJECT_ID, S.STVN,
CASE
WHEN UL.PRC IS NULL THEN '0'
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE NOT NA_DV IS NULL) AND S.STVN = 3 AND UL.PRC < 100
GROUP BY UP.VAR_ID, UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
ELSE UL.PRC END AS PRC,
S.FULL_NAME||'('||S.STVN||' сек.)' AS FULL_NAME, S.OBJECT_ID||'_'||S.STVN AS ID_LOK
FROM DNCDL.URLB_POEZD UP
JOIN DNNSI.SOST S ON UP.OBJECT_ID = S.OBJECT_ID AND UP.SOSTAVN = S.STVN
JOIN DNCDL.URLB_OSN UO ON UP.ID_URLB = UO.ID_URLB AND UP.VAR_ID = UO.VAR_ID
LEFT JOIN DNCDL.UOL_LOK UL ON UO.ID_UOL = UL.ID_UOL AND UP.SOSTAVN = UL.STVN AND
UP.OBJECT_ID = UL.OBJECT_ID AND UO.VAR_ID = UL.VAR_ID
WHERE UP.ID_URLB IN ( SELECT ID_URLB
FROM DNCDL.URLB_OSN
WHERE NOT NA_DV IS NULL) AND S.STVN = 3 AND UL.PRC < 100
GROUP BY UP.VAR_ID, UP.OBJECT_ID, UP.SOSTAVN, S.FULL_NAME, S.STVN, S.OBJECT_ID, UL.PRC, UL.ID_PREDPR
