SQL & PL/SQL

Hi,

I'm trying insert into table rows but I've problems. I explain the tables and the problem.

I have old table with duplicates with this structure and values:

CREATE TABLE controls (

ID               VARCHAR(12) NOT NULL,

fdate            VARCHAR(10) NOT NULL,

fhour              VARCHAR(5) NOT NULL,

place             VARCHAR(50) NOT NULL,

code       NUMBER(4),

date_code VARCHAR(10),

matter         VARCHAR(14),

answer        VARCHAR(20)

);

the values are similar as these:

The first table that I insert is:

CREATE TABLE register

(

  fdate VARCHAR2(10) NOT NULL,

  ID VARCHAR2(12) NOT NULL,

  fhour VARCHAR(5) NOT NULL,

  place  VARCHAR2(50) NOT NULL,

  CONSTRAINT PK_REGISTER PRIMARY KEY(fdate,ID),

);

And the insert in this table is:

insert into register(fdate,ID,fhour,place)

select fdate, id, max(fhour), max(fplace) from controls group by (fdate,ID);

For example: between these rows:

only insert the second one because the hour is greather.

The matter is in insert on the second table.

CREATE TABLE EVIDENCES

(

  fdateVARCHAR2(10) NOT NULL,

  ID VARCHAR2(12) NOT NULL,

  matter VARCHAR2(14),

  answer VARCHAR2(20) NOT NULL,

  code NUMBER(4) NOT NULL,

  date_code VARCHAR2(10) NOT NULL,

  CONSTRAINT CK_code CHECK (code>0),

  CONSTRAINT PK_EVIDENCES PRIMARY KEY (code,  date_code),

  CONSTRAINT FK_EVIDENCES FOREIGN KEY (date, ID) REFERENCES register (date, ID)

);

the insertion instruction I use is:

insert into EVIDENCES (fdate, ID, matter, answer, code, date_code)

  select distinct fdate, ID, matter, answer, code, date_code from controls

  where answer is not null and code is not null and date_code is not null;

In the examples following:

only insert  this row because the hour is the first. The other registers don't must in the evidences table.

Please help with my insertion code.

Thanks in advance.