Объявление

Свернуть
Пока нет объявлений.

примеры запросов к базе нр sd для миграции данных в от

Свернуть
X
  • Фильтр
  • Время
  • Показать
Очистить всё
новые сообщения

  • примеры запросов к базе нр sd для миграции данных в от

    В качестве примера привожу запрос, необходимый для извлечения данных по заявкам (объект Service Call). Что с этим делать - смотри сообщение http://omniway.ru/forum/viewtopic.php?f=4&t=49

    Но поскольку:
    а) конфигурирование SD может привести к изменению схемы базы и
    б) в различных конфигурациях какие-то поля могут использоваться, а какие-то — нет,
    запросы необходимо модифицировать, чтобы они возвращали ровно те данные, которые нужны. Кроме того, кое-где в них присутствует специфика конкретного проекта.

    Service Calls (Заявки)
    Код:
    SELECT
    CAST(dbo.ITSM_SERVICECALLS.SER_ID AS NVARCHAR(20)) AS 'ServiceDesk_ID'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_OID AS NVARCHAR(20)) AS 'ServiceDesk_OID'
    ,dbo.ITSM_SERVICECALLS.SER_DESCRIPTION
    ,dbo.ITSM_SER_INFORMATION.SEI_INFORMATION
    ,dbo.ITSM_SERVICECALLS.SER_ACTUALSTART
    ,dbo.ITSM_SERVICECALLS.SER_ACTUALFINISH
    ,dbo.ITSM_SERVICECALLS.SER_DEADLINE
    ,CAST(dbo.ITSM_SERVICECALLS.SER_CLO_OID AS NVARCHAR(20)) AS 'SER_CLO_OID'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_ASS_WOG_OID AS NVARCHAR(20)) AS 'SER_ASS_WOG_OID'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_ASS_PER_TO_OID AS NVARCHAR(20)) AS 'SER_ASS_PER_TO_OID'
    ,dbo.REP_CODES_TEXT.RCT_NAME AS 'STATUS'
    ,ITSM_CODES_LOCALE2.CDL_NAME AS 'MEDIUM'
    ,'IMPACT' = CASE
    WHEN ITSM_CODES_LOCALE3.CDL_NAME LIKE {{N}}'Низкая ( 1 person affected)' THEN {{N}}'Низкая'
    WHEN ITSM_CODES_LOCALE3.CDL_NAME LIKE {{N}}'Средняя (Group / Unit affected)' THEN {{N}}'Средняя'
    WHEN ITSM_CODES_LOCALE3.CDL_NAME LIKE {{N}}'Высокая (Department affected)' THEN {{N}}'Высокая'
    WHEN ITSM_CODES_LOCALE3.CDL_NAME LIKE {{N}}'Чрезвычайная (Site / Organization affected)' THEN {{N}}'Чрезвычайная'
    END
    ,{{REPLACE}}(ITSM_CODES_LOCALE4.CDL_NAME, '.', '') AS 'PRIORITY'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_CIT_OID AS NVARCHAR(20)) AS 'SER_CIT_OID'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_CALLER_PER AS NVARCHAR(20)) AS 'SER_CALLER_PER_OID'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_POO_OID AS NVARCHAR(20)) AS 'SER_IT_SERVICE_OID(cat)'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_CAT_OID AS NVARCHAR(20)) AS 'CATEGORY'
    ,CAST(dbo.ITSM_SERVICECALLS.SER_INITIATOR_PER_OID AS NVARCHAR(20)) AS 'SER_INITIATOR_PER_OID'
    ,dbo.ITSM_SERVICECALLS.SER_FREQUENTLYASKEDQUESTION
    ,dbo.ITSM_SER_4K1.SE1_4K1 AS 'IT_SPECIALIST_COMMENT'
    ,dbo.ITSM_SER_4K1.SE1_4K1 AS 'IT_SPECIALIST_COMMENT2'
    ,dbo.ITSM_SER_WORKAROUND.SCW_WORKAROUND
    ,dbo.ITSM_SER_CUSTOM_FIELDS.SCF_BOOLEAN10 AS 'SEND_EMAIL_AFTER_REGISTRATION'
    FROM
    dbo.ITSM_SERVICECALLS
    LEFT JOIN dbo.ITSM_SER_4K1 ON dbo.ITSM_SERVICECALLS.SER_OID = dbo.ITSM_SER_4K1.SE1_SER_OID
    LEFT JOIN dbo.ITSM_SER_INFORMATION ON dbo.ITSM_SERVICECALLS.SER_OID = dbo.ITSM_SER_INFORMATION.SEI_SER_OID
    LEFT JOIN dbo.ITSM_SER_WORKAROUND ON dbo.ITSM_SERVICECALLS.SER_OID = dbo.ITSM_SER_WORKAROUND.SCW_SER_OID
    LEFT JOIN dbo.ITSM_SER_CUSTOM_FIELDS ON dbo.ITSM_SERVICECALLS.SER_OID = dbo.ITSM_SER_CUSTOM_FIELDS.SCF_SER_OID
    LEFT JOIN dbo.REP_CODES_TEXT ON dbo.ITSM_SERVICECALLS.SER_STA_OID = dbo.REP_CODES_TEXT.RCT_RCD_OID
    LEFT JOIN dbo.ITSM_CODES_LOCALE AS ITSM_CODES_LOCALE2 ON dbo.ITSM_SERVICECALLS.SER_MED_OID = ITSM_CODES_LOCALE2.CDL_COD_OID
    LEFT JOIN dbo.ITSM_CODES_LOCALE AS ITSM_CODES_LOCALE3 ON dbo.ITSM_SERVICECALLS.SER_IMP_OID = ITSM_CODES_LOCALE3.CDL_COD_OID
    LEFT JOIN dbo.ITSM_CODES_LOCALE AS ITSM_CODES_LOCALE4 ON dbo.ITSM_SERVICECALLS.SER_PRI_OID = ITSM_CODES_LOCALE4.CDL_COD_OID
    REP_CODES_TEXT2.RCT_RCD_OID
    LEFT JOIN dbo.REP_CODES_TEXT AS REP_CODES_TEXT3 ON dbo.ITSM_SERVICECALLS.SER_CLA_OID = REP_CODES_TEXT3.RCT_RCD_OID
    WHERE
    (dbo.REP_CODES_TEXT.RCT_LNG_OID = 1049 OR dbo.ITSM_SERVICECALLS.SER_STA_OID IS NULL)
    AND
    (ITSM_CODES_LOCALE2.CDL_LNG_OID = 1049 OR dbo.ITSM_SERVICECALLS.SER_MED_OID IS NULL)
    AND
    (ITSM_CODES_LOCALE3.CDL_LNG_OID = 1049 OR dbo.ITSM_SERVICECALLS.SER_IMP_OID IS NULL)
    AND
    (ITSM_CODES_LOCALE4.CDL_LNG_OID = 1049 OR dbo.ITSM_SERVICECALLS.SER_PRI_OID IS NULL)
    --AND
    --(REP_CODES_TEXT2.RCT_LNG_OID = 1049 OR dbo.ITSM_SERVICECALLS.SER_CAT_OID IS NULL)
    AND
    dbo.REP_CODES_TEXT.RCT_NAME NOT LIKE {{N}}'Закрыто'
    ORDER BY
    dbo.ITSM_SERVICECALLS.SER_ID
    С уважением,
    Савичев Илья
    OmniWay
    http://www.omniway.ru
Обработка...
X