1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| SELECT
TAB1.ID_CURRENCY_POS,
DBDWH.DIM_S_CURRENCY.UK_CURRENCY,
DBDWH.DIM_S_CURRENCY.ID_CURRENCY
FROM
(
SELECT
CAST(ID_PERIODE AS INTEGER) ID_PERIODE,
ID_MONNAIE ID_CURRENCY_LCL,
ID_MONNAIE ID_CURRENCY_POS,
ID_PRODUIT ID_PRODUCT,
CAST(ID_GENRE AS CHAR(3)) ID_SECURITY_CLASS,
CAST(ID_DEPOSITAIRE AS CHAR(4)) ID_CUSTODIAN,
CAST((CASE ID_TYPE WHEN 'E' THEN 1 WHEN 'T' THEN 2 ELSE 0 END) AS CHAR(1)) FK_ASSET_TYPE,
CAST(ID_SIGLE_VTLMIS AS CHAR(4)) ID_MIS_ASSET_BREAKDOWN,
CAST(ID_SIGLE_VTLMIS_FDS AS CHAR(4)) ID_MIS_ASSET_BREAKDOWN_FDS,
CAST(ID_CAT_PLMT AS CHAR(3)) ID_INVESTMENT_CATEGORY,
ID_RACINE ID_COUNTERPART,
NUMERO_ORDRE C_ORDRE_NUMBER,
ID_MONNAIE,ID_DOSSIER CD_FILE,ORIGIN_POS CD_ORIGIN,
CAST(LPAD(DATE_LIQUIDATION ,8,'0')AS CHAR(8)) S_DT_LIQUIDATION,
CAST(ID_DEPOSITAIRE AS CHAR(4)) ID_DEPOSITAIRE,ID_TYPE,
CAST(MNT_CONTREVALEUR AS DECIMAL(20,4)) MNT_POSITION_CURRENCY_LCL,
CAST(MNT_DEVISE AS DECIMAL(20,4)) MNT_POSITION_CURRENCY_POS,
CAST(MNT_DEVISE_EST AS DECIMAL(20,4)) MNT_POSITION_CURRENCY_PERF ,
CAST(INTER_CRUS_CHF AS DECIMAL(20,4)) MNT_ACCRUAL_INTEREST_LCL,
CAST(INTER_CRUS_EST AS DECIMAL(20,4)) MNT_ACCRUAL_INTEREST_PERF,
FORTUNE_BRUTE FLG_GROSS_ASSET,
FORTUNE_NETTE FLG_NET_ASSET,
DATE_DERNIER_MVT DT_LAST_MOVEMENT,
CAST(QTY AS DECIMAL(20,4)) MNT_QUANTITY,
CAST(QTE_TIT_PREN AS DECIMAL(20,4)) MNT_QUANTITY_PRE_RECORDED,
COURS_CHANGE MNT_RATE_EXCHANGE_POS_LCL,
COURS_CHANGE_EST MNT_RATE_EXCHANGE_PERF_LCL,
COURS_TITRE S_MNT_SHARE_PRICE,
COURS_ACHAT_DEV_TIT S_MNT_AVERAGE_PURCHASE_PRICE_CURR_SEC,
COURS_ACHAT_DEV_OP S_MNT_AVERAGE_PURCHASE_PRICE_CURR_POS,
DT_DER_ACV S_DT_LAST_BUY_SELL,
MNT_PERF_HIST_DEV_TIT S_MNT_PERF_HIST_CURR_SEC,
MNT_PERF_HIST_DEV_OP S_MNT_PERF_HIST_CURR_POS,
MNT_PERF_YTD_DEV_TIT S_MNT_PERF_YTD_CURR_SEC,
MNT_PERF_YTD_DEV_OP S_MNT_PERF_YTD_CURR_POS,
MNT_PERF_DRU_HIST_DEV_TIT S_MNT_PERF_DRU_CURR_SEC,
MNT_PERF_DRU_HIST_DEV_OP S_MNT_PERF_DRU_CURR_POS,
MNT_PERF_DRU_YTD_DEV_TIT S_MNT_PERF_DRU_YTD_CURR_SEC,
MNT_PERF_DRU_YTD_DEV_OP S_MNT_PERF_DRU_YTD_CURR_POS,
RDT_ECH_FIN S_MNT_DRU_YIELD
FROM
DBBI.UFH_POSI_POSITIONFI
WHERE ID_PERIODE = 20151231
AND (MNT_CONTREVALEUR IS NOT NULL
OR MNT_DEVISE IS NOT NULL)) TAB1, DBDWH.DIM_S_CURRENCY
LEFT OUTER JOIN DBDWH.DIM_S_CURRENCY ON TAB1.ID_CURRENCY_POS = DBDWH.DIM_S_CURRENCY.ID_CURRENCY; |
Partager