-- -----------------------------------------------------
-- Materialized view prd_trace_view
-- -----------------------------------------------------

DROP TABLE IF EXISTS `prd_trace_view`;

CREATE TABLE IF NOT EXISTS `prd_trace_view` (
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `date` date NOT NULL,
  `user` varchar(12) NOT NULL,
  `tcode` varchar(40) NOT NULL,
  PRIMARY KEY (`system`,`client`,`date`,`user`,`tcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO prd_trace_view
SELECT h.system, client, date, user, tcode
FROM prd_trace_header AS h JOIN prd_trace_detail AS d 
ON d.hdr_id=h.id 
GROUP BY h.system, client, date, user, tcode;
