Cześć,
Oracle SQL
Czy mógłby mi ktoś podpowiedzieć jakiej składni użyć. Spójrzcie na pogrubiony warunek. Co chcę osiągnąć.
Przykładowo dla warunku:
WHEN (T1.PL_PAY_DATE - T0.INVOICE_DATE) BETWEEN 31 AND 60 THEN '31_60'
chciałbym aby została utworzona nowa kolumna '31_60' i do tej kolumny została przeniesiona wartość z kolumny T0.INVOICE_AMOUNT, dla faktury, która spełnia ten warunek, tzn.:
przykładowo wartość w kolumnie 'OKRES_SPLATY' dla tej faktury/ danej = 35. To samo dla pozostałych warunków.
SELECT T0.IDENTITY AS NR_KONTRAHENTA, T0.SUPPLIER_NAME AS NAZWA_KONTRAHENTA, T0.ASSOCIATION_NO AS NIP, T0.VOUCHER_DATE_REF AS DATA_PK, T0.VOUCHER_NO_REF AS NUMER_PK, T0.VOUCHER_TYPE_REF AS TYP_PK
,T0.ACCOUNTING_YEAR_REF AS ROK, T0.PRELIM_CODE AS KOD_WST, T0.LEDGER_ITEM_ID AS NUMER_FAKTURY, T0.LEDGER_ITEM_SERIES_ID AS TYP_FAKTURY,
CASE
WHEN T1.CORRECTION_INVOICE = 'FALSE' THEN 'NIE'
WHEN T1.CORRECTION_INVOICE = 'TRUE' THEN 'TAK'
END AS CZY_FAKT_KORYG,
CASE
WHEN T1.CORRECTION_EXISTS = 'FALSE' THEN 'NIE'
WHEN T1.CORRECTION_EXISTS = 'TRUE' THEN 'TAK'
END AS CZY_JEST_KOREKTA
,T1.NCF_REFERENCE AS ODN_DO_PLATN, T0.PAY_TERM_BASE_DATE AS DATA_WAR_PLATN, T0.PAY_TERM_ID AS WAR_PLATN, T0.INVOICE_DATE AS DATA_FAKTURY, T0.ARRIVAL_DATE AS DATA_WPLYWU, T0.DUE_DATE AS NOWY_TERMIN_PŁATNOSCI, T0.FIRST_PLANNED_DATE AS NAST_PLAN_DATA_PLATN
,T1.PL_PAY_DATE AS PLANOW_DATA_PLATN,
CASE
WHEN T0.ONE_TIME = 'FALSE' THEN 'NIE'
WHEN T0.ONE_TIME = 'TRUE' THEN 'TAK'
END AS CZY_PLATN_O_CZASIE,
CASE
WHEN T1.INSTALLMENT_PLAN = 'FALSE' THEN 'NIE'
WHEN T1.INSTALLMENT_PLAN = 'TRUE' THEN 'TAK'
END AS SYSTEM_RATALNY
,T0.NET_AMOUNT AS KWOTA_NETTO_FAKT, T0.VAT_AMOUNT AS KWOTA_VAT, T0.INVOICE_AMOUNT AS KWOTA_FAKTURY, T0.ACTUAL_NET_AMOUNT AS AKT_ZAPL_KWOTA, T0.OPEN_AMOUNT AS KWOTA_OTWARTA, (T1.PL_PAY_DATE - T0.INVOICE_DATE) AS ILOSC_DNI,
**<u>CASE
WHEN (T1.PL_PAY_DATE - T0.INVOICE_DATE) <= 30 THEN '0_30'
END AS TEST))
WHEN (T1.PL_PAY_DATE - T0.INVOICE_DATE) BETWEEN 31 AND 60 THEN '31_60'
WHEN (T1.PL_PAY_DATE - T0.INVOICE_DATE) BETWEEN 61 AND 120 THEN '61_120'
WHEN (T1.PL_PAY_DATE - T0.INVOICE_DATE) >= 121 THEN 'POW_120'
END AS OKRES_SPLATY</u>**
,T0.INV_STATE AS STATUS_FAKTURY, T0.AUTH_ID AS AUTORYZACJA
FROM IFSAPP.INVOICE_LEDGER_ITEM_SU_QRY T0
JOIN IFSAPP.INCOMING_INVOICE2 T1 ON T0.INVOICE_ID = T1.INVOICE_ID
WHERE T0.INVOICE_DATE between to_date( '20220101', 'YYYYMMDD' ) and to_date( '20221231', 'YYYYMMDD' ) + ( 1 - 1/ ( 60*60*24 ) )
--AND T0.LEDGER_ITEM_ID = '0007/06/2022/FVS'
ORDER BY T0.INVOICE_DATE ASC;
Pozdrawiam