Jay,
That is
very interesting! I adapted to mny tables.fields situation and indeed it works! As a comparison, the SQL+Procedural method I outlined took an average of .411 to produce 504 records. Your straight SQL solution took .210 for the same 504 records!
Although not a discernible difference to the user, your solution is twice as fast!
Although I am not yet sure if I will change the program to your solution as it seems a little more complicated to understand (maybe) and creating a Cartesian product goes against the grain, it was indeed ewye opening.
Thanks for the exercise!
>>>>>>>>>>>
>Alex,
>
>Don't want to beat a dead horse after your success.
>
>But, the question you raised interested me just on its own merits.
>
>I came up with a solution, and I'd like to go through it.
>
>The problem requires:
>
>1. A Cartesian join between all of the "attribute" tables -- date, country and transaction code. We're usually not supposed to do this, but, hey, this is time! That gives all possible combinations of the 3 attributes.
>
>2. A set subtraction from the cartesian join where you have data.
>
>3. A union between 2 and the data you have.
>
>It took a bit of tinkering, because SQL kept spitting up on "not supported" when trying to compare concatenations of the 3 fields in a nested select.
>
>If you don't mind, I'm just going to put in the code that I developed along the same lines. You can extrapolate from there.
>
>>* Create a cursor of all combinations of country, state and saletype
>* (Cartesian join). Note that there is no WHERE clause.
>* The AllFields field will be used for comparison purposes in a nested SELECT.
>
>SELECT Country.Country, State.State, SaleType.SaleType, Country.Country + State.State + SaleType.SaleType AS Allfields ;
> FROM Country, State, SaleType ;
> INTO CURSOR OthTables
>
>* Create a cursor containing the concatenation of the 3 fields contained
>* in the Order table -- this will be used in a nested SELECT.
>SELECT Ord.Country + Ord.State + Ord.SaleType AS AllFields ;
> FROM Ord ;
> INTO CURSOR Ord2
>
>* Combine the data you have with the complete set minus the combinations
>* already with quantities.
>SELECT Ord.Country, Ord.State, Ord.SaleType, Ord.Quantity ;
> FROM Ord ;
>UNION ;
> SELECT OthTables.Country, OthTables.State, OthTables.SaleType, 000000000 AS Quantity ;
> FROM OthTables WHERE OthTables.AllFields NOT IN (SELECT Ord2.AllFields FROM Ord2) ;
>INTO CURSOR Answer
>
>
>Thanks for the problem!
>
> Jay
>>>>>>>>>>>
