Hi

> I'd like to create a query that returns the 'history' of configurations
> for one machine, like this:

Assume:

CREATE TABLE TEST (
MACHINE_NAME VARCHAR(20) NOT NULL,
CONFIG_NAME VARCHAR(20),
WORK_DATE DATE NOT NULL
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (MACHINE_NAME, WORK_DATE);

INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_1', '2012-11-01');
INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_1', '2012-11-02');
INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_1', '2012-11-03');
INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_2', '2012-11-04');
INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_2', '2012-11-05');
INSERT INTO TEST (MACHINE_NAME, CONFIG_NAME, WORK_DATE) VALUES ('MACHINE_1',
'CONFIG_1', '2012-11-06');

Then your query could be:

SELECT A.CONFIG_NAME,
( SELECT FIRST 1 B.WORK_DATE FROM TEST B
WHERE B.CONFIG_NAME = A.CONFIG_NAME
AND B.MACHINE_NAME = 'MACHINE_1'
AND B.WORK_DATE < A.WORK_DATE
AND NOT EXISTS ( SELECT 1 FROM TEST C
WHERE C.MACHINE_NAME = 'MACHINE_1'
AND C.CONFIG_NAME <> B.CONFIG_NAME
AND C.WORK_DATE < A.WORK_DATE AND C.WORK_DATE >
B.WORK_DATE )
ORDER BY WORK_DATE ASC ) AS DATE_FROM,
A.WORK_DATE AS DATE_TO
FROM TEST A WHERE A.MACHINE_NAME = 'MACHINE_1'
AND ( ( SELECT FIRST 1 D.CONFIG_NAME FROM TEST D WHERE D.MACHINE_NAME =
'MACHINE_1' AND D.WORK_DATE > A.WORK_DATE ORDER BY WORK_DATE ASC ) <>
A.CONFIG_NAME
OR NOT EXISTS ( SELECT 1 FROM TEST E WHERE E.MACHINE_NAME = 'MACHINE_1'
AND E.WORK_DATE > A.WORK_DATE ) )

Which might even be fast, but this is something done way better in a stored
procedure:

CREATE PROCEDURE HISTORY (
MACHINE_NAME VARCHAR(20))
RETURNS (
CONFIG_NAME VARCHAR(20),
DATE_FROM DATE,
DATE_TO DATE)
AS
declare variable cur_config_name varchar(20);
declare variable cur_work_date date;
begin
select first 1 config_name, work_date from test
where machine_name = :machine_name
order by work_date
into :config_name, :date_from;
date_to = date_from;
for select config_name, work_date from test
where machine_name = :machine_name
order by work_date
into :cur_config_name, :cur_work_date
do begin
if ( cur_config_name <> config_name ) then begin
suspend;
config_name = cur_config_name;
date_from = cur_work_date;
end
date_to = cur_work_date;
end
if ( config_name is not null ) then
suspend;
end

And then just call:
select * from history( 'MACHINE_1' )

You won't get much more efficient than that.

Regards,
Steffen



[Non-text portions of this message have been removed]