Sergey,
Another field is included. Currently, I am joining on equality, but I need to change that.
My current join looks like this:
select tmpReport.*,;
LiquidationPrice.*;
from tmpReport left join LiquidationPrice;
on bintoc(tmpReport.artparent) + dtos(tmpReport.PlanMonday);
= bintoc(liquidationprice.article) + dtos(liquidationprice.planweek);
into cursor tmpReport
Additional information:
TmpReport is the result (cursor) of a previous join.
The reason I use bintoc() and dtos() is because, to optimize the query, I have an index (table PlanWeek) on "BINTOC(article)+DTOS(planweek)".
For the new structure, I want to get one and only one record from LiquidationPrice. If the date in field "PlanMonday" doesn't exist in the second table, I want to search back for the closest date.
I am not sure whether this can be done exclusively with SQL - SELECT commands, even if the date were the only join condition. Or, if it can, whether it would be efficient.
Hilmar.
>Hi Hilmar,
>
>Is a date one and only join condition? Can you provide little bit more info about relationship between those two tables?
>
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)