>I have a blind spot today. Can't seem to see the forest for the trees and can't come up with the proper SELECT-SQL. I want to create a denormalized temp table to be the basis of a report.
>
>Transactions table has a summary of transactions per day,per countery, per type:
>FileDate D
>Country C(25)
>TCode C(3)
>TranCount N(6,0)
>
>I also have a table of all available countries where this product is sold (6 countries only) and a table of possible TCodes (7 records like "DES", "LDS", "PUR", "ANU", "XLD"...)
>
>Not all countries have sales in all Transaction codes (TCode) in all dates. I want to get a join of Transactions with Countries and Tcodes to get NULL entries for all unused Tcodes and unused Countries in the Transactions list for each date.
>

>e.g.:
>06/01/01 COLOMBIA DES 4
>06/01/01 COLOMBIA LDS 6
>06/01/01 PERU     DES 6
>06/02/01 BRAZIL   PUR 6
>06/02/01 PERU     PUR 3
>

>
>I want to fill in the missing records to complete all missing codes in each country and all missing countries in each day.
>
>Is there a query or trwo to do this? Or am I better off doin procedural code and SCAN..ENDSCAN to INSERT missing records?
>
>I need the report table denormalized, as opposed to doing a loop when creating the report, and adding missing data, as I will not do the reporting myself but export data to an XLS file for somebody else to deal with.

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