· 8 years ago · Mar 14, 2018, 12:54 AM
1+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
2| CML_ID | POF_COLUMN | CML_TYPE | SAMPLE_VALUE | COMPLIANCE | CORROSION_SEVERITY | LR_LD | POF |
3+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
4| 1 | SAMPLE_VALUE | MIC_SAMPLING_POINT | 5 | NO | MINOR | 1 | |
5| 2 | SAMPLE_VALUE | MIC_SAMPLING_POINT | 0.5 | NO | MINOR | 2 | |
6| 3 | SAMPLE_VALUE | MIC_SAMPLING_POINT | 20 | NO | MINOR | 3 | |
7| 4 | COMPLIANCE | VALVE_ROTATED | 0 | YES | MINOR | 4 | |
8| 5 | LR_LD | PIPING_THICKNESS | 0 | YES | MINOR | 0.1 | |
9| 6 | CORROSION_SEVERITY | VESSEL_SHELL | 0 | NO | SEVERE | 0 | |
10| 7 | CORROSION_SEVERITY | NOZZLE | 0 | NO | LOW | 0 | |
11+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
12
13+--------------------+--------------------+-------------+-----+
14| POF_COLUMN | CML_TYPE | VALUE_RANGE | POF |
15+--------------------+--------------------+-------------+-----+
16| SAMPLE_VALUE | MIC_SAMPLING_POINT | 1 | 5 |
17| SAMPLE_VALUE | MIC_SAMPLING_POINT | 5 | 4 |
18| SAMPLE_VALUE | MIC_SAMPLING_POINT | 10 | 3 |
19| SAMPLE_VALUE | MIC_SAMPLING_POINT | 15 | 2 |
20| SAMPLE_VALUE | MIC_SAMPLING_POINT | 100 | 1 |
21| COMPLIANCE | VALVE_ROTATED | YES | 5 |
22| COMPLIANCE | VALVE_ROTATED | NO | 1 |
23| LR_LD | PIPING_THICKNESS | 2 | 5 |
24| LR_LD | PIPING_THICKNESS | 1.5 | 4 |
25| LR_LD | PIPING_THICKNESS | 1 | 3 |
26| LR_LD | PIPING_THICKNESS | 0.8 | 2 |
27| LR_LD | PIPING_THICKNESS | 0.5 | 1 |
28| CORROSION_SEVERITY | VESSEL_SHELL | NEGLIGIBLE | 5 |
29| CORROSION_SEVERITY | VESSEL_SHELL | LOW | 4 |
30| CORROSION_SEVERITY | VESSEL_SHELL | MEDIUM | 3 |
31| CORROSION_SEVERITY | VESSEL_SHELL | HIGH | 2 |
32| CORROSION_SEVERITY | VESSEL_SHELL | SEVERE | 1 |
33| CORROSION_SEVERITY | NOZZLE | NEGLIGIBLE | 5 |
34| CORROSION_SEVERITY | NOZZLE | LOW | 5 |
35| CORROSION_SEVERITY | NOZZLE | MEDIUM | 5 |
36| CORROSION_SEVERITY | NOZZLE | HIGH | 3 |
37| CORROSION_SEVERITY | NOZZLE | SEVERE | 2 |
38+--------------------+--------------------+-------------+-----+
39
40+--------+---+-----+
41| CML_ID | … | POF |
42+--------+---+-----+
43| 1 | … | 4 |
44| 2 | … | 5 |
45| 3 | … | 2 |
46| 4 | … | 5 |
47| 5 | … | 1 |
48| 6 | … | 1 |
49| 7 | … | 5 |
50+--------+---+-----+
51
52DROP DATABASE IF EXISTS VESSELS;
53CREATE DATABASE VESSELS;
54USE VESSELS;
55
56CREATE TABLE CML (
57 `CML_ID` INTEGER NOT NULL,
58 `POF_COLUMN` VARCHAR(50),
59 `CML_TYPE` VARCHAR(50),
60 `SAMPLE_VALUE` FLOAT,
61 `COMPLIANCE` ENUM('YES','NO'),
62 `CORROSION_SEVERITY` VARCHAR(50),
63 `LR_LD` FLOAT,
64 `POF` TINYINT,
65 PRIMARY KEY (CML_ID)
66);
67
68CREATE TABLE POF (
69 `POF_COLUMN` VARCHAR(50),
70 `CML_TYPE` VARCHAR(50),
71 `VALUE_RANGE` VARCHAR(100),
72 `POF` TINYINT,
73 PRIMARY KEY (POF_COLUMN, CML_TYPE, VALUE_RANGE)
74);
75
76INSERT INTO CML
77VALUES (1, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 'NO', 'MINOR', 1, NULL),
78 (2, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 0.5, 'NO', 'MINOR', 2, NULL),
79 (3, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 20, 'NO', 'MINOR', 3, NULL),
80 (4, 'COMPLIANCE', 'VALVE_ROTATED', 0, 'YES', 'MINOR', 4, NULL),
81 (5, 'LR_LD', 'PIPING_THICKNESS', 0, 'YES', 'MINOR', 0.1, NULL),
82 (6, 'CORROSION_SEVERITY', 'VESSEL_SHELL', 0, 'NO', 'SEVERE', 0, NULL),
83 (7, 'CORROSION_SEVERITY', 'NOZZLE', 0, 'NO', 'LOW', 0, NULL);
84
85INSERT INTO POF
86VALUES ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 1, 5),
87 ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 4),
88 ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 10, 3),
89 ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 15, 2),
90 ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 100, 1),
91 ('COMPLIANCE', 'VALVE_ROTATED', 'YES', 5),
92 ('COMPLIANCE', 'VALVE_ROTATED', 'NO', 1),
93 ('LR_LD', 'PIPING_THICKNESS', 2, 5),
94 ('LR_LD', 'PIPING_THICKNESS', 1.5, 4),
95 ('LR_LD', 'PIPING_THICKNESS', 1, 3),
96 ('LR_LD', 'PIPING_THICKNESS', 0.8, 2),
97 ('LR_LD', 'PIPING_THICKNESS', 0.5, 1),
98 ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'NEGLIGIBLE', 5),
99 ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'LOW', 4),
100 ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'MEDIUM', 3),
101 ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'HIGH', 2),
102 ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'SEVERE', 1),
103 ('CORROSION_SEVERITY', 'NOZZLE', 'NEGLIGIBLE', 5),
104 ('CORROSION_SEVERITY', 'NOZZLE', 'LOW', 5),
105 ('CORROSION_SEVERITY', 'NOZZLE', 'MEDIUM', 5),
106 ('CORROSION_SEVERITY', 'NOZZLE', 'HIGH', 3),
107 ('CORROSION_SEVERITY', 'NOZZLE', 'SEVERE', 2);
108
109-- UPDATE EXACT MATCHING VALUE
110UPDATE CML c
111LEFT JOIN POF p
112ON c.POF_COLUMN = p.POF_COLUMN
113 AND c.CML_TYPE = p.CML_TYPE
114 AND ( (c.POF_COLUMN = 'COMPLIANCE' AND c.COMPLIANCE = p.VALUE_RANGE) OR
115 (c.POF_COLUMN = 'SAMPLE_VALUE' AND c.SAMPLE_VALUE = p.VALUE_RANGE) OR
116 (c.POF_COLUMN = 'LR_LD' AND c.LR_LD = p.VALUE_RANGE) OR
117 (c.POF_COLUMN = 'CORROSION_SEVERITY' AND c.CORROSION_SEVERITY = p.VALUE_RANGE)
118 )
119SET c.POF = p.POF;
120
121SELECT * FROM CML;