USER
Привет, я дам тебе описание узла Expression Transformation из Informatica Power Center, твоя задача
воссоздать по этому узлу ORACLE SQL запрос.
То есть - все поля, которые приходят - берутся из какой-то таблицы, а дальше над ними проделывают манипуляции
Обзор Трансформации EXPTRANS_VX
Тип трансформации: 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
Описание: Входное/выходное поле, вероятно, классификатор или категория.Обзор Трансформации EXPTRANS_VX
Тип трансформации: 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
Описание: Входное/выходное поле, вероятно, классификатор или категория.ASSISTANT
Создание точного Oracle SQL запроса, который полностью воспроизводит логику трансформации из Informatica Power Center, представляет собой сложную задачу из-за использования последовательных переменных и зависимостей между строками данных. В среде Informatica выражения используют локальные переменные, которые сохраняют состояние между обработкой строк, что не напрямую поддерживается в стандартном SQL.
Однако мы можем попытаться воссоздать логику с использованием аналитических функций и рекурсивных CTE (Common Table Expressions). Ниже представлен Oracle SQL запрос, который приближается к требуемой логике:
```sql
WITH source_data AS (
SELECT
st.*,
ROW_NUMBER() OVER (ORDER BY ACNT_CONTRACT__ID, POSTING_DATE, M_TRANSACTION__ID) AS RN
FROM
source_table st
),
recursive_calc (RN, ACCOUNT__OID, ACNT_CONTRACT__ID, M_TRANSACTION__ID, POSTING_DATE, BAL, CCAT,
DAY_ARR_OD, REPORT_DATE, CONT_v, POSTING_DATE_v, ACCT_v, LIST_ACCT, STEP, LAST_ROW_v,
STEP_O, LAST_ROW_o, LIST_ACCT_O) AS (
-- Начальная строка
SELECT
RN,
ACCOUNT__OID,
ACNT_CONTRACT__ID,
M_TRANSACTION__ID,
POSTING_DATE,
BAL,
CCAT,
CASE WHEN BAL = 0 THEN 0 ELSE POSTING_DATE - (TO_DATE('04.10.2024','DD.MM.YYYY') + 1) END AS DAY_ARR_OD,
TO_DATE('04.10.2024','DD.MM.YYYY') AS REPORT_DATE,
ACNT_CONTRACT__ID AS CONT_v,
POSTING_DATE AS POSTING_DATE_v,
TO_CHAR(ACCOUNT__OID) AS ACCT_v,
TO_CHAR(ACCOUNT__OID) AS LIST_ACCT,
0 AS STEP,
0 AS LAST_ROW_v,
0 AS STEP_O,
0 AS LAST_ROW_o,
TO_CHAR(ACCOUNT__OID) AS LIST_ACCT_O
FROM
source_data
WHERE
RN = 1
UNION ALL
-- Рекурсивная часть
SELECT
sd.RN,
sd.ACCOUNT__OID,
sd.ACNT_CONTRACT__ID,
sd.M_TRANSACTION__ID,
sd.POSTING_DATE,
sd.BAL,
sd.CCAT,
CASE WHEN sd.BAL = 0 THEN 0 ELSE sd.POSTING_DATE - (TO_DATE('04.10.2024','DD.MM.YYYY') + 1) END AS DAY_ARR_OD,
TO_DATE('04.10.2024','DD.MM.YYYY') AS REPORT_DATE,
sd.ACNT_CONTRACT__ID AS CONT_v,
sd.POSTING_DATE AS POSTING_DATE_v,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN TO_CHAR(sd.ACCOUNT__OID)
ELSE rc.ACCT_v || ' ' || TO_CHAR(sd.ACCOUNT__OID)
END AS ACCT_v,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN TO_CHAR(sd.ACCOUNT__OID)
WHEN rc.CONT_v = sd.ACNT_CONTRACT__ID AND INSTR(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID)) <> 0 AND sd.BAL <> 0 THEN rc.LIST_ACCT
WHEN rc.CONT_v = sd.ACNT_CONTRACT__ID AND INSTR(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID)) = 0 AND sd.BAL <> 0 THEN rc.LIST_ACCT || TO_CHAR(sd.ACCOUNT__OID)
WHEN rc.CONT_v = sd.ACNT_CONTRACT__ID AND INSTR(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID)) <> 0 AND sd.BAL = 0 THEN REGEXP_REPLACE(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID), '')
ELSE ''
END AS LIST_ACCT,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN 0
ELSE
CASE
WHEN sd.BAL = 0 AND REGEXP_REPLACE(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID), '1') = '1' THEN rc.STEP + 1
WHEN sd.BAL <> 0 AND rc.LIST_ACCT = '' AND sd.POSTING_DATE = rc.POSTING_DATE_v AND INSTR(rc.ACCT_v, TO_CHAR(sd.ACCOUNT__OID)) = 0 AND sd.CCAT <> 'C' THEN rc.STEP - 1
ELSE rc.STEP
END
END AS STEP,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN 0
ELSE rc.LAST_ROW_v + 1
END AS LAST_ROW_v,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN 0
ELSE
CASE
WHEN sd.BAL = 0 AND REGEXP_REPLACE(rc.LIST_ACCT, TO_CHAR(sd.ACCOUNT__OID), '1') = '1' THEN rc.STEP + 1
WHEN sd.BAL <> 0 AND rc.LIST_ACCT = '' AND sd.POSTING_DATE = rc.POSTING_DATE_v AND INSTR(rc.ACCT_v, TO_CHAR(sd.ACCOUNT__OID)) = 0 AND sd.CCAT <> 'C' THEN rc.STEP - 1
ELSE rc.STEP
END
END AS STEP_O,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN 0
ELSE rc.LAST_ROW_v + 1
END AS LAST_ROW_o,
CASE
WHEN rc.CONT_v <> sd.ACNT_CONTRACT__ID OR rc.CONT_v IS NULL THEN TO_CHAR(sd.ACCOUNT__OID)
ELSE rc.ACCT_v || ' ' || TO_CHAR(sd.ACCOUNT__OID)
END AS LIST_ACCT_O
FROM
source_data sd
INNER JOIN recursive_calc rc ON sd.RN = rc.RN + 1
)
SELECT
ACCOUNT__OID,
ACNT_CONTRACT__ID,
M_TRANSACTION__ID,
POSTING_DATE,
BAL,
CCAT,
DAY_ARR_OD,
REPORT_DATE,
STEP_O,
LAST_ROW_o,
LIST_ACCT_O
FROM
recursive_calc
ORDER BY
RN;
```
**Пояснения:**
- **source_data:** Инициализация данных с присвоением номера строки с помощью `ROW_NUMBER()`.
- **recursive_calc:** Рекурсивное выражение, которое выполняет последовательные вычисления, опираясь на результаты предыдущей строки.
- **JOIN recursive_calc ON sd.RN = rc.RN + 1:** Обеспечивает последовательную обработку строк в порядке номера.
- **Вычисление полей DAY_ARR_OD и REPORT_DATE:** Выполняется напрямую, так как они не зависят от предыдущих значений.
- **Вычисление переменных (например, ACCT_v, LIST_ACCT, STEP):** Основано на значениях из предыдущей строки `rc` и текущей строки `sd`.
- **Использование функций CASE и REGEXP_REPLACE:** Для воссоздания логики условий и операций над строками.
**Важно отметить:**
- Данный подход может иметь ограничения по производительности на больших объемах данных из-за рекурсии.
- Рекурсивные CTE в Oracle имеют ограничение по глубине рекурсии (по умолчанию 1000). Для обхода этого ограничения можно установить большее значение с помощью `SET RECURSIVE WITH LIMIT`.
**Рекомендации:**
- Если это возможно, рассмотреть использование PL/SQL процедуры или скрипта для выполнения подобных вычислений, где можно использовать переменные и циклы.
- Уточнить порядок данных и добавить соответствующие индексы для оптимизации запроса.
- Провести тестирование на небольшом наборе данных перед применением на полном объеме.
---
Этот запрос представляет собой приближение и может потребовать доработки в зависимости от конкретных данных и среды выполнения. Важно протестировать и убедиться, что результаты совпадают с ожидаемыми из Informatica Power Center.