SQL Imbriqué DB2

Bonjour,

Je tente d'exécuter la requête suivante:

Code:

---

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;

---

Et renvoi d'un failed :

Citation:

---

A reference to column "DBDWH.DIM_S_CURRENCY.ID_CURRENCY" is ambiguous.. SQLCODE=-203, SQLSTATE=42702, DRIVER=4.16.53

---

Pouvez-vous m'aider svp?

Bonjour,

C'est du au fait que vous utilisez 2 fois la table DIM_S_CURRENCY dans votre requete, du coup, faute d'alias, l'interpréteur ne sait pas à quelle occurrence se rapporte votre colonne

Ajoutez des alias et le problème n'apparaitra plus

De plus, je ne vois aucun critère de jointure entre la 1ère utilisation de DIM_S_CURRENCY et la requete imbriquée dont l'alias est TAB1 :weird:
Edit : J'ai mis en couleur ci dessous le code manquant, et en gras, la 2ème utilisation de la même table, qui rend nécessaire l'utilisation de 2 alias

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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, [. . .] 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, INNER JOIN ou LEFT (OUTER) JOIN DBDWH.DIM_S_CURRENCY ON ????? LEFT OUTER JOIN DBDWH.DIM_S_CURRENCY ON TAB1.ID_CURRENCY_POS = DBDWH.DIM_S_CURRENCY.ID_CURRENCY;

---

Hello !

Au petit bonheur la chance je dirais qu'il faut remplacer ce lignes:

Code:

---

1 2	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;

---

par ca :

Code:

---

1 2	OR MNT_DEVISE IS NOT NULL)) TAB1 LEFT OUTER JOIN DBDWH.DIM_S_CURRENCY ON TAB1.ID_CURRENCY_POS = DBDWH.DIM_S_CURRENCY.ID_CURRENCY;

---

Par ailleurs, je ne vois pas l’intérêt de la sous requete ;)
cette requete ne donnerait elle pas le meme résultat ? :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT TAB1.ID_MONNAIE ID_CURRENCY_POS, DBDWH.DIM_S_CURRENCY.UK_CURRENCY, DBDWH.DIM_S_CURRENCY.ID_CURRENCY FROM DBBI.UFH_POSI_POSITIONFI TAB1 LEFT OUTER JOIN DBDWH.DIM_S_CURRENCY ON TAB1.ID_CURRENCY_POS = DBDWH.DIM_S_CURRENCY.ID_CURRENCY WHERE ID_PERIODE = 20151231 AND (MNT_CONTREVALEUR IS NOT NULL OR MNT_DEVISE IS NOT NULL);

---