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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| DROP MATERIALIZED VIEW APOLLO_REP_UK.CDIM_PRODUCT_ATT_MV;
CREATE MATERIALIZED VIEW APOLLO_REP_UK.CDIM_PRODUCT_ATT_MV
TABLESPACE APOLLO_REP_UK
PCTUSED 0
PCTFREE 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 16K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH COMPLETE
START WITH TO_DATE('28-déc.-2011 15:36:51','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1
WITH PRIMARY KEY
AS
/* Formatted on 2011/12/27 16:30 (Formatter Plus v4.8.8) */
SELECT DISTINCT pr.product_sid, pr.brand_sid, pr.brand_name,
UPPER (NVL (sn.text_value, 'N/A')) season,
UPPER (NVL (hl.text_value, 'N/A')) heavy_light,
UPPER (NVL (ot.text_value, 'N/A')) original_tread_depth,
UPPER (NVL (nr.text_value, 'N/A')) new_remould,
UPPER (NVL (wp.text_value, 'N/A')) POSITION,
NVL (li.numeric_value, 0) load_index,
UPPER (NVL (si.text_value, 'N/A')) speed_index,
UPPER (NVL (se.numeric_value, 0))
|| ' / '
|| UPPER (NVL (ap.numeric_value, 0))
|| ' / '
|| UPPER (NVL (rd.numeric_value, 0)) dimensions,
UPPER (NVL (pn.text_value, 'N/A')) profile_name,
CASE
WHEN UPPER (NVL (hl.text_value, 'N/A')
) = 'LIGHT'
THEN 1.0
ELSE 1.6
END AS minimum_tread_depth
FROM apollo_rep_uk.dim_product pr LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes hl
ON pr.product_sid = hl.product_sid
AND hl.esm_attribute_name LIKE '001%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes nr
ON pr.product_sid = nr.product_sid
AND nr.esm_attribute_name LIKE '002%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes sn
ON pr.product_sid = sn.product_sid
AND sn.esm_attribute_name LIKE '017%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes wp
ON pr.product_sid = wp.product_sid
AND wp.esm_attribute_name LIKE '023%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes si
ON pr.product_sid = si.product_sid
AND si.esm_attribute_name LIKE '009%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes li
ON pr.product_sid = li.product_sid
AND li.esm_attribute_name LIKE '008%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes se
ON pr.product_sid = se.product_sid
AND se.esm_attribute_name LIKE '003%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes ap
ON pr.product_sid = ap.product_sid
AND ap.esm_attribute_name LIKE '004%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes rd
ON pr.product_sid = rd.product_sid
AND rd.esm_attribute_name LIKE '005%'
LEFT OUTER JOIN APOLLO_REP_UK.dim_product_attributes ot
ot pr.product_sid = ot.product_sid
AND ot.esm_attribute_name LIKE '026%'
LEFT OUTER JOIN apollo_rep_uk.dim_product_attributes pn
ON pr.product_sid = pn.product_sid
AND pn.esm_attribute_name LIKE '006%';
COMMENT ON MATERIALIZED VIEW APOLLO_REP_UK.CDIM_PRODUCT_ATT_MV IS 'snapshot table for snapshot APOLLO_REP_UK.CDIM_PRODUCT_ATT_MV'; |
Partager