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
| WITH TABLE_DES_COMMANDES AS
( SELECT 1001 num_cde, 'art_a' code_article, 100 code_couleur, 10 qte_t1, 0 qte_t2, 30 qte_t3, TO_DATE('10/02/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 1002 num_cde, 'art_b' code_article, 200 code_couleur, 0 qte_t1, 50 qte_t2, 10 qte_t3, TO_DATE('20/02/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 1003 num_cde, 'art_c' code_article, 300 code_couleur, 2 qte_t1, 10 qte_t2, 0 qte_t3, TO_DATE('02/03/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 1004 num_cde, 'art_d' code_article, 400 code_couleur, 50 qte_t1, 11 qte_t2, 0 qte_t3, TO_DATE('10/03/2018', 'DD/MM/RRRR') date_de_creation FROM dual),
TABLE_DES_CODESBARRE AS
(SELECT 'art_a' code_article, 100 code_couleur, 1 numero_taille, 3663254567895 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
-- UNION ALL SELECT 'art_a' code_article, 100 code_couleur, 2 numero_taille, 3663254567896 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_a' code_article, 100 code_couleur, 3 numero_taille, 3663254567897 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_b' code_article, 200 code_couleur, 1 numero_taille, 3663254567898 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_b' code_article, 200 code_couleur, 2 numero_taille, 3663254567899 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_b' code_article, 200 code_couleur, 3 numero_taille, 3663254567900 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_c' code_article, 300 code_couleur, 1 numero_taille, 3663254567901 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_c' code_article, 300 code_couleur, 2 numero_taille, 3663254567902 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_c' code_article, 300 code_couleur, 3 numero_taille, 3663254567903 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_d' code_article, 400 code_couleur, 1 numero_taille, 3663254567904 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_d' code_article, 400 code_couleur, 2 numero_taille, 3663254567905 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_d' code_article, 400 code_couleur, 3 numero_taille, 3663254567906 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_e' code_article, 500 code_couleur, 1 numero_taille, 3663254567907 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_e' code_article, 500 code_couleur, 2 numero_taille, 3663254567908 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual
UNION ALL SELECT 'art_e' code_article, 500 code_couleur, 3 numero_taille, 3663254567909 code_barre, TO_DATE('05/01/2018', 'DD/MM/RRRR') date_de_creation FROM dual)
SELECT cde.*, cb.code_barre, cb.date_de_creation AS date_crea_cb
FROM TABLE_DES_COMMANDES UNPIVOT EXCLUDE NULLS (val FOR taille IN (qte_t1 AS 1, qte_t2 AS 2, qte_t3 AS 3) ) cde, TABLE_DES_CODESBARRE cb
WHERE cb.code_article (+)= cde.code_article AND cb.code_couleur (+)= cde.code_couleur AND cb.numero_taille (+)= cde.taille
AND cde.num_cde = 1001 |
Partager