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'; |
