Alex --
There are many ways to skin a Fox! What's nice is that it allows a variety of idioms and you can make it work to fit your programming style.
Thanks for checking that out on your data -- interesting to see the impact in a "real world" situation.
I'm going to keep that problem in the back of my mind -- I'm sure I'll run into it again!
Jay
>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
>>>>>>>>>>>>
