-- Last update: Oct 18, 2007
-- Database: `cemipat`
--
-- BY JULIO RAFFO (EPFL/CEMI)
--
-- --------------------------------------------------------

CREATE TABLE  cemipat.ipc_coding (
  `ipc_class_symbol` char(15) character set utf8,
  `ipc_id` smallint(6)
);

LOAD DATA INFILE 'E:/scripts/patstat/ipc/ipc_coding.csv'
INTO TABLE cemipat.ipc_coding
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES ;

ALTER TABLE `cemipat`.`ipc_coding`
 ADD PRIMARY KEY (`ipc_id`),
 ADD INDEX index_symbol(`ipc_class_symbol`);

create table cemipat.t209_appln_ipc_bis as (
SELECT t9.appln_id, i.ipc_id
FROM cemipat.ipc_coding i inner join patstat.tls209_appln_ipc t9
on i.ipc_class_symbol=t9.ipc_class_symbol);

ALTER TABLE cemipat.t209_appln_ipc_bis
 ADD INDEX index_1(appln_id),
 ADD INDEX index_2(ipc_id);