Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Привет, твоя задача - воссоздать трансформацию из Informatica Power Center на Oracle…

  1. User

    Привет, твоя задача - воссоздать трансформацию из Informatica Power Center на Oracle SQL. Мне нужно воссоздать функционал из Informatica Power Center на Oracle SQL При этом тебе нельзя использовать переменные в скрипте и нельзя использовать рекурсивные CTE, можешь использовать оконные функции вроде LISTAGG, LAG и других

    Вот информация о трансформации

    Тип трансформации: Expression Имя: EXPTRANS_VX Версия объекта: 1 Перепользуемый: Нет Номер версии: 1 Детальный Разбор Полей Поле: ACCOUNT__OID Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy ACCOUNT__OID Описание: Входное/выходное поле, передающее идентификатор аккаунта. Поле: ACNT_CONTRACT__ID Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy ACNT_CONTRACT__ID Описание: Входное/выходное поле, передающее идентификатор контракта аккаунта. Поле: M_TRANSACTION__ID Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy M_TRANSACTION__ID Описание: Входное/выходное поле, передающее идентификатор транзакции. Поле: POSTING_DATE Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy POSTING_DATE Описание: Входное/выходное поле, передающее дату проводки. Поле: BAL Тип данных: Decimal Точность: 28 Масштаб: 2 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy BAL Описание: Входное/выходное поле, передающее баланс. Поле: DAY_ARR_OD Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext

    Copy IIF (BAL = 0, 0, DATE_DIFF(POSTING_DATE, ADD_TO_DATE(TO_DATE('04.10.2024','DD.MM.YYYY'), 'DAY',1), 'DD')) Описание: Вычисляет разницу в днях между датой проводки и заданной датой 04.10.2024, если баланс не равен нулю. Если баланс равен нулю, возвращает 0. Поле: STEP Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy IIF(CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), 0, IIF( BAL = 0 AND REG_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), '1') = '1', STEP + 1, IIF( BAL <> 0 AND LIST_ACCT = '' AND POSTING_DATE = POSTING_DATE_v AND INSTR(ACCT_v, TO_CHAR(ACCOUNT__OID)) = 0 AND CCAT <> 'C', STEP - 1, STEP ) ) ) Описание: Логика определения шага (STEP) на основе условий сравнения различных полей и переменных. Примеры условий включают проверку баланса, наличия аккаунта в списке и соответствия дат проводки. Поле: LIST_ACCT Тип данных: String Точность: 2500 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy IIF( CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), TO_CHAR(ACCOUNT__OID), IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) <> 0 AND BAL <> 0, LIST_ACCT, IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) = 0 AND BAL <> 0, LIST_ACCT || TO_CHAR(ACCOUNT__OID), IIF( CONT_v = ACNT_CONTRACT__ID AND INSTR(LIST_ACCT, TO_CHAR(ACCOUNT__OID)) <> 0 AND BAL = 0, REG_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), ''), '' ) ) ) ) Описание: Формирует список аккаунтов (LIST_ACCT) на основе условий сравнения контрактного ID, наличия аккаунта в списке, баланса и других параметров. Поле: STEP_O Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext

    Copy STEP Описание: Выходное поле, передающее значение шага (STEP). Поле: LAST_ROW_v Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy IIF(CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), 0, LAST_ROW_v + 1) Описание: Логика для определения последней строки (LAST_ROW_v). Если контрактный ID не совпадает или CONT_v пуст, устанавливает 0, иначе увеличивает LAST_ROW_v на 1. Поле: LAST_ROW_o Тип данных: Decimal Точность: 10 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext

    Copy LAST_ROW_v Описание: Выходное поле, передающее значение последней строки (LAST_ROW_v). Поле: POSTING_DATE_v Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy POSTING_DATE Описание: Локальная переменная для хранения даты проводки. Поле: ACCT_v Тип данных: NString Точность: 2500 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy IIF( CONT_v <> ACNT_CONTRACT__ID OR ISNULL(CONT_v), TO_CHAR(ACCOUNT__OID), ACCT_v || ' ' || TO_CHAR(ACCOUNT__OID) ) Описание: Формирует строку аккаунтов (ACCT_v) на основе условий сравнения контрактного ID и наличия аккаунта. Поле: CONT_v Тип данных: Decimal Точность: 18 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy ACNT_CONTRACT__ID Описание: Локальная переменная для хранения контрактного ID. Поле: SUM_ACCT_v Тип данных: Decimal Точность: 28 Масштаб: 0 Тип порта: Локальная переменная Выражение (Формула): plaintext

    Copy SUM_ACCT_v Описание: Локальная переменная для хранения суммы по аккаунтам. Поле: REPORT_DATE Тип данных: Date/Time Точность: 29 Масштаб: 9 Тип порта: Output Выражение (Формула): plaintext

    Copy TO_DATE('04.10.2024','DD.MM.YYYY') Описание: Выходное поле, содержащее фиксированную дату отчёта 04.10.2024. Поле: LIST_ACCT_O Тип данных: String Точность: 2500 Масштаб: 0 Тип порта: Output Выражение (Формула): plaintext

    Copy ACCT_v Описание: Выходное поле, передающее список аккаунтов (ACCT_v). Поле: CCAT Тип данных: String (nstring в первой версии) Точность: 1 Масштаб: 0 Тип порта: Input/Output Выражение (Формула): plaintext

    Copy CCAT Описание: Входное/выходное поле, вероятно, классификатор или категория.

    Вот код таблицы, из которой берутся данные для трансформации

    CREATE TABLE u14626.SRTTRANS_To_CHECK_v1 ( ACNT_CONTRACT__ID number(18), POSTING_DATE date, M_TRANSACTION__ID number(18), ACCOUNT__OID number(18), BAL number(28,2), CCAT varchar2(1) );

  2. Assistant

    Привет! Понял задачу. Нужно преобразовать логику трансформации из Informatica Power Center в Oracle SQL, используя оконные функции и избегая переменных и рекурсивных CTE. Постараюсь воспроизвести основные элементы трансформации, насколько это возможно с помощью SQL.

    Общий Подход

    1. Поля-передатчики: ACCOUNT__OID, ACNT_CONTRACT__ID, M_TRANSACTION__ID, POSTING_DATE, BAL, CCAT — просто передаются из исходной таблицы.

    2. Вычисляемое поле DAY_ARR_OD: Используем CASE для реализации логики IIF.

    3. Поля STEP, LIST_ACCT и другие переменные: Эти поля зависят от накопительной логики, которая в SQL без рекурсии и переменных реализуется ограниченно. Попробуем использовать оконные функции для эмуляции этой логики.

    Реализация SQL Запроса

    WITH base AS (
        SELECT
            ACCOUNT__OID,
            ACNT_CONTRACT__ID,
            M_TRANSACTION__ID,
            POSTING_DATE,
            BAL,
            CCAT,
            -- Вычисление DAY_ARR_OD
            CASE 
                WHEN BAL = 0 THEN 0
                ELSE TRUNC(POSTING_DATE) - TO_DATE('04.10.2024', 'DD.MM.YYYY') - 1
            END AS DAY_ARR_OD,
            -- Порядок строк для оконных функций
            ROW_NUMBER() OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY POSTING_DATE, ACCOUNT__OID) AS rn
        FROM 
            u14626.SRTTRANS_To_CHECK_v1
    )
    , list_acct AS (
        SELECT
            b.*,
            -- Составление списка ACCOUNT__OID до текущей строки
            LISTAGG(b.ACCOUNT__OID, ',') WITHIN GROUP (ORDER BY b.rn) OVER (PARTITION BY b.ACNT_CONTRACT__ID ORDER BY b.rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LIST_ACCT
        FROM 
            base b
    )
    , step_calc AS (
        SELECT
            la.*,
            -- Вычисление шага STEP с использованием накопительной суммы
            SUM(
                CASE 
                    WHEN (CONT_v <> ACNT_CONTRACT__ID OR CONT_v IS NULL) THEN 0
                    WHEN BAL = 0 AND REGEXP_REPLACE(LIST_ACCT, TO_CHAR(ACCOUNT__OID), '1') = '1' THEN 1
                    WHEN BAL <> 0 AND LIST_ACCT = '' AND POSTING_DATE = POSTING_DATE_v
                         AND INSTR(ACCT_v, TO_CHAR(ACCOUNT__OID)) = 0
                         AND CCAT <> 'C' THEN -1
                    ELSE 0
                END
            ) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS STEP
        FROM (
            SELECT
                la.*,
                -- Предыдущие значения для переменных
                LAG(ACNT_CONTRACT__ID) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS CONT_v,
                LAG(POSTING_DATE) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS POSTING_DATE_v,
                LAG(LIST_ACCT) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS LIST_ACCT_v,
                LAG(BAL) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS BAL_v,
                LAG(CCAT) OVER (PARTITION BY ACNT_CONTRACT__ID ORDER BY rn) AS CCAT_v
            FROM 
                list_acct la
        ) la
    )
    SELECT
        sc.ACCOUNT__OID,
        sc.ACNT_CONTRACT__ID,
        sc.M_TRANSACTION__ID,
        sc.POSTING_DATE,
        sc.BAL,
        sc.CCAT,
        sc.DAY_ARR_OD,
        sc.STEP,
        -- Пример использования LIST_AGCT
        sc.LIST_ACCT,
        -- Выходные поля
        sc.STEP AS STEP_O,
        -- LAST_ROW_o можно определить как ROW_NUMBER
        ROW_NUMBER() OVER (PARTITION BY sc.ACNT_CONTRACT__ID ORDER BY sc.rn DESC) AS LAST_ROW_o,
        TO_DATE('04.10.2024','DD.MM.YYYY') AS REPORT_DATE,
        sc.LIST_ACCT AS LIST_ACCT_O
    FROM 
        step_calc sc
    ORDER BY 
        sc.ACNT_CONTRACT__ID, sc.rn;
    

    Пояснения

    1. CTE base: Основной набор данных с добавлением вычисления DAY_ARR_OD и нумерации строк (ROW_NUMBER) для каждой группы ACNT_CONTRACT__ID.

    2. CTE list_acct: Использует LISTAGG для составления списка ACCOUNT__OID до текущей строки в пределах каждой группы ACNT_CONTRACT__ID.

    3. CTE step_calc: Пытается эмулировать логику вычисления STEP с помощью оконной функции SUM. Здесь используется CASE для определения, когда увеличивать или уменьшать шаг. Также применяются функции LAG для получения предыдущих значений необходимых переменных.

    4. Основной SELECT: Передает необходимые поля и добавляет выходные поля, включая фиксированную дату отчёта и список аккаунтов.

    Ограничения и Допущения

    • Эмуляция Логики STEP: Полная эмуляция сложной логики STEP из Informatica без использования рекурсивных CTE или переменных затруднительна. В представленном решении использована накопительная сумма (SUM) с условиями, что может не полностью соответствовать исходной логике.

    • Переменные CONT_v, POSTING_DATE_v и др.: Используются функции LAG для получения предыдущих значений, что приближает к использованию переменных, но не является идентичным механизмом.

    • Функция REG_REPLACE: В Oracle используется REGEXP_REPLACE. В примере заменены на REGEXP_REPLACE, но возможно потребуется уточнение регулярного выражения.

    • Список Аккаунтов LIST_ACCT: Предполагается, что разделитель , подходит для всех случаев. Если требуются другие разделители или формат, это нужно скорректировать.

    • Поле LAST_ROW_o: Реализовано как ROW_NUMBER в обратном порядке, что может отличаться от логики LAST_ROW_v + 1 в исходной трансформации.

    Заключение

    Данный SQL-запрос пытается воспроизвести логику трансформации из Informatica Power Center, используя возможные оконные функции Oracle SQL. Однако, из-за характера некоторых вычислений, требующих пошаговой обработки и состояния между строками, полная идентичность функционала может быть недостижима без использования рекурсивных CTE или процедурного кода. Рекомендуется протестировать данный запрос на ваших данных и внести необходимые корректировки.