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
|
CREATE TABLE TST181207
(
UNIT_NAME VARCHAR2(50 CHAR),
TAG_NUMBER VARCHAR2(20 CHAR),
MR_NAME VARCHAR2(15 CHAR)
);
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('38 ABC1', '38XY 00602', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('38 ABC2', '38XY 01000', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('38 ABC3', '38XY 01403', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('38 ABC4', '38XY 01406', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('42 DEF1', '42XY 03850', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('38 ABC5', '41XY 03850', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('42 DEF2', '42XY 03850', 'TS01JD-0001');
Insert into TST181207
(UNIT_NAME, TAG_NUMBER, MR_NAME)
Values
('42 DEF3', '42XY 03850', 'TS01JD-0001');
COMMIT;
----------
WITH basedata
AS (SELECT a.*
, SUBSTR (a.unit_name, 1, 2) unit_name_id
, SUBSTR (a.tag_number, 1, 2) tag_number_id
, COUNT (SUBSTR (a.tag_number, 1, 2)) OVER (PARTITION BY a.mr_name, SUBSTR (a.unit_name, 1, 2)) ct_tag_number_id
FROM TST181207 a)
SELECT a.*, CASE WHEN a.unit_name_id = a.tag_number_id THEN 'bien!' ELSE 'pas bien!' END check_if_error
FROM basedata a
, ( SELECT unit_name_id
FROM basedata
WHERE ct_tag_number_id = (SELECT MAX (ct_tag_number_id) FROM basedata)
GROUP BY unit_name_id) b
WHERE a.unit_name_id = b.unit_name_id; |
Partager