Step 1 uses nPath to create conversion counts.
CREATE MULTISET TABLE attrconv AS ( SELECT ( IND_EMLOP || IND_CLKNI || IND_CLKIN || IND_CLKAC || IND_CLKCO) AS IND, num_conv FROM ( SELECT case when num_EMLOP > 0 then '1' ELSE '0' END AS IND_EMLOP , CASE WHEN num_CLKNI > 0 THEN '1' ELSE '0' END AS IND_CLKNI , CASE WHEN num_CLKIN > 0 THEN '1' ELSE '0' END AS IND_CLKIN , CASE WHEN num_CLKAC > 0 THEN '1' ELSE '0' END AS IND_CLKAC , CASE WHEN num_CLKCO > 0 THEN '1' ELSE '0' END AS IND_CLKCO , COUNT (conversion_evt) AS num_conv FROM nPath@coprocessor ( ON attrib PARTITION BY ind ORDER BY ts USING MODE (nonoverlapping) PATTERN ('(B|C|D|E|F)+.(act|ape|aps|apu|fun|rea){1}') SYMBOLS ( int_type = 'EMLOP' AS B, int_type = 'CLKNI' AS C, int_type = 'CLKIN' AS D, int_type = 'CLKAC' AS E, int_type = 'CLKCO' AS F, int_type = 'ACTVD' AS act, int_type = 'APEAP' AS ape, int_type = 'APEST' AS aps, int_type = 'APESU' AS apu, int_type = 'FUNDD' AS fun, int_type = 'REACT' AS rea ) RESULT ( FIRST (ind OF ANY (B,C,D,E,F)) AS ind, COUNT (int_type OF B) AS num_EMLOP, COUNT (int_type OF C) AS num_CLKNI, COUNT (int_type OF D) AS num_CLKIN, COUNT (int_type OF E) AS num_CLKAC, COUNT (int_type OF F) AS num_CLKCO, LAST_NOTNULL (int_type OF ANY (act,ape,aps,apu,fun,rea)) AS conversion_evt, ACCUMULATE (int_type OF ANY(B,C,D,E,F) ) AS intractn_path ) ) AS dt GROUP BY 1,2,3,4,5 ) a ) WITH DATA;
SELECT * FROM attrconv ORDER BY ind;
IND num_conv ----- -------- 00001 126 00010 132 00011 29 00100 124 00101 25 00110 34 00111 7 01000 130 01001 32 01010 26 01011 11 01100 31 01101 19 01110 7 01111 5 10000 111 10001 27 10010 27 10011 8 10100 30 10101 4 10110 8 10111 1 11000 29 11001 5 11010 9