--- In firebird-support@yahoogroups.com, "ndirondello marco" wrote:

>
> hi, i'm not very good with sql and i have a problem with a query.
>
> i'm using fb 2.5 superclassic on win xp with default configuration.
>
> i have this 2 tables
>
> source
> (
> ID Bigint NOT NULL, PK
> SOURCE_ID Bigint NOT NULL
> SOURCE_TYPE Smallint NOT NULL
> COUNT Integer NOT NULL
> )
>
> data
> (
> ID Bigint NOT NULL, PK
> START_TIME Timestamp NOT NULL
> SOURCE_ID Bigint NOT NULL,
> SOURCE_TYPE Smallint NOT NULL,
> NAME varchar NOTNULL,
> VALUE Bigint NOT NULL
> )
>
> with the index on data START_TIME DESC and unique index on data
> (SOURCE_ID SOURCE_TYPE, NAME)
>
> for every row in souce i need to check the last source.COUNT rows
> of data (joined on source_id and source_type) ordered by
> data.START_TIME desc,
> if the value of this COUNT rows are all > 0 i need to select the
> first row daa and return it.
>
> this is the pseudo-sql that i'm using (and that will return what i
> want)
>
> ValidSource (rows that exceed the check) =
> select s.SOURCE_ID, s.SOURCE_TYPE
> from source s
> join data d
> on s.SOURCE_ID = d.SOURCE_ID
> and s.SOURCE_TYPE = d.SOURCE_TYPE
> where d.VALUE > 0
> and d.Id in
> (
> select d2.Id
> from data d2
> where s.SOURCE_ID = d2.SOURCE_ID
> and s.SOURCE_TYPE = d2.SOURCE_TYPE
> order by d2.START_TIME desc
> rows s.COUNT
> )
> group by s.SOURCE_ID, s.SOURCE_TYPE
> having count(s.SOURCE_ID) = s.COUNT
>
> LastValidValues =
> for each row in ValidSource vs
> select d.*
> from data d
> where d.SOURCE_ID = vs.SOURCE_ID
> and d.SOURCE_TYPE = vs.SOURCE_TYPE
> order by d.START_TIME desc
> rows 1
>
> return LastValidValues
>
> my problem is that data table contains a lot of data so the query
> to get ValidSource take a lot of time (2/3 minutes).

Hmm, seems a bit trickier than I at first thought. Maybe you need EXECUTE BLOCK similar to (probably full of syntax errors, I never use EXECUTE BLOCK myself):

EXECUTE BLOCK
RETURNS (SOURCE_ID INT, SOURCE_TYPE VARCHAR(255))
AS
DECLARE MyCOUNT INT;
DECLARE MyVALUE INT;
DECLARE OK INT;
BEGIN
FOR SELECT s.SOURCE_ID, s.SOURCE_TYPE, s.Count
from source s
INTO :SOURCE_ID, SOURCE_TYPE, MyCOUNT
DO
BEGIN
OK = 1;
FOR SELECT d.VALUE
from data d
where d.SOURCE_ID = :SOURCE_ID
and d.SOURCE_TYPE = :SOURCE_TYPE
into :MyValue
order by d.START_TIME desc
rows :MyCount
DO
BEGIN
IF (MyValue = 0) then OK = 0;
END
IF (OK = 1) THEN SUSPEND;
END
END

Note that I changed your requirement a little bit, I checked that none of the x first data rows contained 0, if they may contain NULL, you have to change a little bit, if there may be less than COUNT matching rows in DATA then my logic breaks down.

HTH,
Set