Dataphor - Passing a table as a parameter

A quick peek at what passing a table as a parameter really means?

This example uses the D4 language of Dataphor, MS Sql Server 2005, 
the Orders and OrderDetails tables from the Northwind db.

Consider the following two simple sql queries which use a group by 
column and return the maximum value of a column for each group.

select top 5 EmployeeID,Max(Freight) as MaxFrt
from Orders
group by EmployeeID
order by MaxFrt

EmployeeID  MaxFrt                
----------- --------------------- 
6           367.6300
8           398.3600
1           544.0800
4           719.7800
9           754.2600

select top 5 ProductID,Max(UnitPrice) as MaxUP
from OrderDetails
group by ProductID
order by MaxUP

ProductID   MaxUP                 
----------- --------------------- 
33          2.5000
24          4.5000
13          6.0000
52          7.0000
54          7.4500

We can put each query in a stored procedure and return all the rows.

create procedure OrdersMaxFrt
as
select EmployeeID,Max(Freight) as MaxFrt
from Orders
group by EmployeeID

create procedure OrderDetailsMaxUP
as
select ProductID,Max(UnitPrice) as MaxUP
from OrderDetails
group by ProductID

Given that the queries concern themselves with 2 mutually exclusive
sets of facts (tables) what can be said of any similarities
between them? It's clear they perform the same general operation,
they both group the data by a single column and return a max of
single column. So they return rows consisting of two columns.
We can say (loosely speaking) each sp returns a table. What's left?
Look at the columns of each query:

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders' 

TABLE_NAME COLUMN_NAME DATA_TYPE 
---------- ----------- --------- 
Orders     EmployeeID  int       
Orders     Freight     money     
Orders     OrderID     int       

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'OrderDetails' 

TABLE_NAME   COLUMN_NAME DATA_TYPE 
------------ ----------- --------- 
OrderDetails OrderID     int       
OrderDetails ProductID   int       
OrderDetails UnitPrice   money     

Each query uses a group by column of int(eger) and a max of a 
money column. Each query returns a table of an int column and
a money. Each table involves a column (OrderID) of int in the 
primary key definition. All these similarities can be seen as
'structural' similarities. And when we talk of such similarities
we are talking the language of 'type' just as we would say the
number 5 is an int type. But in this context we are speaking
of 'table' type.
We can now take all these structural ideas and create a D4
procedure that performs the group by operation on a table that
is passed as a parameter.

create operator
  GetaMax(aTable:table{aPkCol:Integer,aGroupCol:Integer,aMoneyCol:Money}):
                table{aGroupCol:Integer,aMax:Money}
begin
result:=
  aTable group by{aGroupCol} add {Max(aMoneyCol) aMax};
end;

The operand (argument) aTable is a table defined in terms of its 
column name and corresponding data type:

table{aPkCol:Integer,aGroupCol:Integer,aMoneyCol:Money}

The procedure returns a result which is also a table:

table{aGroupCol:Integer,aMax:Money}

And now we can answer the question of what passing a table as a 
parameter really means. It just means the definition of the table
in terms of its structure, its column names and their data type.
By contrast, the only reference to a table in sql is by its name.
The structural use of table information in sql does not exist. 
In other words, there is no table 'type' in sql and therefore
nothing on which a table parameter could be based on. In sql there
is only the literal representation of a table (its name) whereas 
in D4 it can be represented as a variable. It is the same difference 
representing the literal string 'ABC' but not being able to assign
'ABC' to a variable of type string. (Note how sql tries to overcome
the lack of a table type: dynamic sql ☻ .)

Ok, now to how the D4 GetaMax procedure works.

The sql stored procedure exec OrdersMaxFrt becomes:

select 
 GetaMax(Orders {OrderID aPkCol,EmployeeID aGroupCol,Freight aMoneyCol})
       {aGroupCol EmployeeID,aMax MaxFrt}
          order by {EmployeeID};

       
EmployeeID MaxFrt    
---------- --------- 
1          $544.08   
2          $810.05   
3          $1,007.64 
4          $719.78   
5          $890.78   
6          $367.63   
7          $830.75   
8          $398.36   
9          $754.26   

In D4 the:

'GetaMax(Orders {OrderID aPkCol,EmployeeID aGroupCol,Freight aMoneyCol})'

means execute the GetaMax operator using the Orders table but renaming
the Orders columns to the table type (the columns with their data types)
declared in the operator. In renaming the columns and because the columns 
have the same corresponding data types as declared in the operator we 
succeed in passing the Orders table (as a variable). So the EmployeeID (int)
column is renamed to aGroupCol and Freight (money) to aMoneyCol 
(same for OrderID). 
The '{aGroupCol EmployeeID,aMax MaxFrt}' simply renames the generic
columns of the operator back to the meaningful column names of the
Orders table.

Likewise, The sql stored procedure exec OrderDetailsMaxUP becomes:

select 
 GetaMax(OrderDetails {OrderID aPkCol,ProductID aGroupCol,UnitPrice aMoneyCol})
      {aGroupCol ProductID,aMax MaxUP}
        order by {ProductID};
        
ProductID MaxUP   
--------- ------- 
1         $18.00  
2         $19.00  
3         $10.00  
4         $22.00  
.

Just as a variable defined as int can take an infinite number of values
the GetaMax procedure represents the same operation on a possibly 
infinite number of tables. As I hope you can imagine the concept of
a table type/argument has far reaching implications for application
development.

Finally, if you have Dataphor you can run the following two batches
to compare the GetaMax operator to the sql stored procedures.
(Note that the D4 operator 'SQLQuery' is used for pass-thru queries
 sending t-sql directly to Sql Server. Also Sql Server converts the max
 of a money type to decimal. To make the comparision with GetaMax it must
 be converted back to money).

//Check Orders.
var Compare_the_Tables:='The 2 tables are different';
if 
  (
    GetaMax(Orders {OrderID aPkCol,EmployeeID aGroupCol,Freight aMoneyCol})
       {aGroupCol EmployeeID, aMax MaxFrt}
   )
  =
(SQLQuery('exec OrdersMaxFrt') {EmployeeID,ToMoney(MaxFrt) MaxFrt}) then
Compare_the_Tables:='The 2 tables are the same';
select Compare_the_Tables;
//The 2 tables are the same

//Check OrderDetails.
var Compare_the_Tables:='The 2 tables are different';
if 
  (
    GetaMax(OrderDetails {OrderID aPkCol,ProductID aGroupCol,UnitPrice aMoneyCol})
      {aGroupCol ProductID,aMax MaxUP}
   )
  =
(SQLQuery('exec OrderDetailsMaxUP') {ProductID,ToMoney(MaxUP) MaxUP}) then
Compare_the_Tables:='The 2 tables are the same';
select Compare_the_Tables;
//The 2 tables are the same

bye for now,
steve