Dataphor - Inclusive Sql vs exclusive D4

Sql questions and solutions are proffered from the perspective of
inclusive. It is generally the largest scope encapsulated in a single
query that is the goal. Every row of every table must be accounted
for. And even those rows that don't exist must be included. If sql
is inclusive then application development must be exclusive. AD
raises a singular question, a particular vendor, a particular
customer. A solution for a specific something need not account
for every something. Therefore it makes sense that AD have constructs
and solutions that match the intent of AD. More on the inclusive vs.
exclusive argument at a later date -:)

Here is a solution in the application development language of Dataphor 
based on the thread:

Monday, June 18, 2007 6:52 AM
microsoft.public.sqlserver.newusers
Query How To?
http://tinyurl.com/yphn44

MS Sql Server 2005 is used to store the data.

We frame the question in an exclusive manner: given a particular player
summarize his ratings over the different sports.

create table WanaPlayers
{
 CID:Integer,
 Player:String {default nil},
 Ethnicity:String {default nil},
 key{CID}
};
insert
 table
     {
      row{1 CID,'Charlie' Player ,'black' Ethnicity},
      row{2, 'Dave', 'white'},
      row{3, 'Josh', 'white'},
      row{4, 'Jeff', 'black'}
     } into WanaPlayers;
     
create table WanaSports
{
 TID:Integer,
 Sport:String {default nil},
 key{TID}
};
insert
 table
     {
      row{1 TID, 'Baseball' Sport},
      row{2, 'Football'},
      row{3, 'Hockey'}
     } into WanaSports;      

create table WanaRatings
{
 RowID:Integer,
 TID:Integer,
 CID:Integer,
 Rating:Integer {default nil},
 key{RowID},
 reference Ratings_Sports {TID} references WanaSports {TID},
 reference Ratings_Players {CID} references WanaPlayers {CID}
}; 
insert
 table
  {
   row{1 RowID,1 TID, 1 CID, 50 Rating},
   row{2,1, 1, 25},
   row{3,2, 1, 69},
   row{4,2, 1, 71},
   row{5,2, 1, 50},
   row{6,3, 1, 50},
   row{7,1, 2, 97},
   row{8,1, 2, 100},
   row{9,1, 2, 100},
   row{10,2, 2, 98},
   row{11,3, 2, 99},
   row{12,1, 3, 0}
  } into WanaRatings; 
  
We create a view using natural joins as opposed to using any outer joins. 
The view simply addresses the maximum rating for each player for each
sport he has.

create view WanaView
 WanaRatings join WanaPlayers join WanaSports
   group by {Player,Sport} add {Max(Rating) MaxRating};  
   
select WanaView;

Player  Sport    MaxRating 
------- -------- --------- 
Charlie Baseball 50        
Charlie Football 71        
Charlie Hockey   50        
Dave    Baseball 100       
Dave    Football 98        
Dave    Hockey   99        
Josh    Baseball 0         

The key of the view is obvious, {Player,Sport}.

Assuming sports baseball, football and hockey we can define an
operator to return a single row with the columns of interest.
For any Player we create a table with their MaxRating for each
sport. We use the key of the view to directly address the max rating.

create operator PlayerRating(Name:String):
       row{Player:String,AvgRating:Decimal,HighNumber:Integer}
begin
result:=row of typeof(result){};
result:=
(table
     {
      row{1 ID,WanaView[Name,'BaseBall' by {Player,Sport}].MaxRating MaxRating},
      row{2,WanaView[Name,'FootBall' by {Player,Sport}].MaxRating},
      row{3,WanaView[Name,'Hockey' by {Player,Sport}].MaxRating}
     } 
      add{ToInteger(MaxRating>80) HighRating}
           group add{Avg(MaxRating) AvgRating,Sum(HighRating) HighNumber}
             {Name Player,AvgRating,HighNumber})[];       
end;

select PlayerRating('Charlie');    

Player  AvgRating HighNumber 
------- --------- ---------- 
Charlie 57        0          

select PlayerRating('Dave');        

Player AvgRating HighNumber 
------ --------- ---------- 
Dave   99        3          

select PlayerRating('Jeff');      

Player AvgRating  HighNumber 
------ ---------- ---------- 
Jeff   <No Value> 0          

select PlayerRating('Steve');      

Player AvgRating  HighNumber 
------ ---------- ---------- 
Steve  <No Value> 0          

Since the operator returns a row we have each column (scalar value)
easily available.

select PlayerRating('Dave').HighNumber;   

3

Feel free to post any questions as comments.

bye for now,
steve