Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Monday, October 22, 2007

Sql - Really simple division

This article is based on the thread:

microsoft.public.sqlserver.programming
Monday, October 22, 2007
"select rows that contain both arg1 = 'x' and arg1 = 'y'"
http://tinyurl.com/386lbx

>I need to select only rows where the name contains x and y.  I do not want
>to get rows that only contain x or contain x, z, or contain y, z.  I only
>want rows where the name has an x an a y (they can have a z but they must
>have an x and a y).

Forget QA or SSMS. Just draw a picture.
You use the term row. So draw a row.

row{aName as sName, acode as code}

Now you want people who have code x and y. These are two
rows. So draw them. Use 'burns' for the person.

row{'burns' as sName, 'x' as code}
row{'burns' as sName, 'y' as code}

Now what do you call two rows together? How about calling it
a table ☺ .
Draw it.

table
   {
    row{'burns' as sName, 'x' as code}
    row{'burns' as sName, 'y' as code}
   }
 
Now if this table, made just for 'burns', is in your
#tmp1 table then 'burns' is a guy you want. In other
words, if both rows (one for x, one for y) are in
#tmp1 then you have a hit. Super simple ☺ .

Now in QA or SSMS do this.

declare @x int, @y int
set @x=2
set @y=3
if @x<=@y
print 'Yes'
else
print 'No'

No explanation necessary. Super simple. Now take the same
idea of comparing two integers and extend it to comparing
two tables.

if
table
     {
      row{'burns' as sName, 'x' as code}
      row{'burns' as sName, 'y' as code}
     }
       <=
         #tmp1
           print 'Yes'
                else 'No'
              
In other words, does each row for 'burns' occur in #tmp1?  
'Burns' can have codes in #tmp1 in addition to x and y (ie. 2<=3)
So 'burns' has to have at least a row for x and a row for y
in #tmp1. If in #tmp1 'burns' has only an 'x' or only a 'y'
no matter what other codes he has that's no good (2<=1).
In the case above you will see 'Yes' printed since the comparison
is true.

This whole scenario is referred to as relational division in
database terminology. But these simple ideas are obscured
by sql because you can't draw a picture of a row, nor a
table nor does sql understand comparing tables like integers.
So instead you're left with grouping and counting, joins,
intersects, existential queries and whatnot all trying to
express a simple idea yet at the same time obscuring it.

Now in a query you want to substitute all the unique names
from #tmp1 into our little table so for each person we can
test the comparison with #tmp1. What would such a query look
like?

select
 select distinct sName as aPerson from #tmp1 
  where      -- draw a table with two rows for each aPerson
             -- the 1st column has a value aPerson and
             -- the column is named 'sName'. The 2nd column is
             -- called 'code'. The column names and datatypes
             -- are the same as in #tmp1.
        table
             {
              row{aPerson as sName, 'x' as code},
              row{aPerson as sName, 'y' as code}
             }
             --  Compare the tables.
              <=
             --  Form a table from #tmp1 of rows belonging to the
             --  aPerson above.
              (Tmp1 where sName=aPerson);

Now this won't quite work in sql no matter where you execute it ☺ .
But what would a query really look like that will work with
#tmp1. Here it is. And it really is almost self-explanatory.
And it works in the D4 language! :)
(Tmp1 is a table same as #tmp1 stored in an Sql Server 2005 database).

select
  Tmp1 {sName aPerson}
    where
         table
              {
               row{aPerson sName, 'x' code},
               row{aPerson sName, 'y' code}
              }
               <=
              (Tmp1 where sName=aPerson);
   
aPerson
-------
burns 
jones 
smith 

Now this is what MS should be doing. Sql is a language of choice
for some things. But it certainly is not the choice language
for others.

Sunday, October 21, 2007

Sql Server - The Undefined Trigger

Ask the question: does Sql Server have a row level trigger.
Answer: it depends. From the point of view of application
development how in the world can the answer possibly be
ambiguous? Well lets back up and look at the Update statement.
From Sql Server 2005 bol:
Using UPDATE with the FROM Clause
'The results of an UPDATE statement are undefined if the
 statement includes a FROM clause that is not specified in
 such a way that only one value is available for each column
 occurrence that is updated, that is if the UPDATE statement
 is not deterministic'.
Ok what is really the rational for even keeping this
'proprietary' syntax? Since it is inherently non-deterministic
why even offer it to developers most of whom do not understand
but the simplest of sql. After all Sql Server is explicitly
relinquishing responsibility for the integrity of the Update and
making the user responsible. Shouldn't the idea of a database be
the other way around? Ok now lets move to the trigger. From bol
under the topic: Multirow Considerations for DML Triggers:
'When you write the code for a DML trigger, consider that the
 statement that causes the trigger to fire can be a single statement
 that affects multiple rows of data, instead of a single row.'
Does this sound familiar? Of course it does. The same non-determinism
of the FROM clause in Update now reappears in the insert table
of the trigger. But of course it is not presented as a 'trigger
is inherently undefined'. No, now the undefined nature of the
trigger is called 'a Multirow consideration'. And again the server
reliquishes responsibility of integrity and puts it in the
hands of the user. Well lets get real. There is no such thing as
a multirow trigger. From an applications standpoint the largest
scope of a trigger is a row. The so called multirow trigger is an 
example of what happens when users don't scrutinize what nonsense
is thrown their way. Instead of getting rid of a stupid idea
in Update using a FROM it was instead extended to a trigger.
It was simply an easy and expedient thing to do. But it was
shameful and lazy too. Lack of integrity is based on lack of
character. But it is equally disappointing that more users do
not complain and demand the integrity that a database should offer.
Sql Server can do much, much better.  But, like E.T., users must
phone home.

Wednesday, October 17, 2007

Sql - Constantly struggling

The subject of this article is called (by others) database constants.
So lets be sure we're on the same page with what a constant is.
It is just a value.

X = Substring(Y,1,5);

X and Y are variables, 1 and 5 are values.

X = Y * 5;

X and Y are variables, 5 is a constant. It is a number whose value is 5.
A table in D4 is just as much a variable as X and Y. A table in 
sql is like the value 5, a constant. The difference between a constant
and a variable is also the difference between D4, a relational
database, and sql!

The following is part of an exchange I had with someone on the
issue of database constants. The idea of database constants
was raised in the article:

A New (and Hopefully Better) Approach to Constants
http://www.sqlservercentral.com/articles/T-SQL/61244/

Here I go ☺ :

Sql Server lets users define a 'table variable'. Now you really
don't believe it's a table 'variable' do you?:)  Of course not.
They're just playing with words. There is no such thing in sql
server as a table variable. But because they call it that most
users really believe it. It's like believing in the tooth fairy:)
Now we're going to play the same game with database 'constants':)
There is no such thing as a database constant. There are only
values (which can't be updated or assigned other values) and
variables (which can be updated and assigned other values).
But in sql there is no real talk of these basic things. And
that's why I talk about Dataphor ☺. There is no difference between
what this guy is calling a 'constant' and a table or 'resultset' in
sql. They are all (constant) values and not variables. I'm trying
to show what sql would be like if there really were table 'variables'.
One way was with the CTE article, 'Do you know what the Sql CTE really is?'
But people seem to be having a hard time wrapping their head
around it:) This sql stuff just gets crazier. Now people are
going to believe that you can pass a table as a parameter to a
stored procedure. That is nonsense. One reason people believe it
is they have nothing to compare/constrast it with. So I show what
it really means to pass a table to a sp:
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html
http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
etc.etc.

But to do these kind of things requires a foundation different than
sql. Hence the name of my site ☺ .
The so called 'constants' are basically a table without a key, ie a row.
So I do:
create table Y
{
 A:Integer,
 B:String,
 key{}
};

insert row{1 A,'Micheal' B} into Y;

select Y[].B
'Michael'
select Y[].A
1
where the '[]' is called a row extracter.
Or I can define a 'list':
var LList:=list(String){'Michael','Steve','Jimmy'};
select LList[0];
Michael

Or I can define a table (ie a row) with lists.
create table Z
{
 Strings:list(String),  //The column holds a list of strings.
 Integers:list(Integer),//The column holds a list of integers.
 key{}
};

insert row{{'Micheal','Steve','Jimmy'} Strings,{2,10,22,40} Integers}
into Z;

select Z[].Strings[2];
Jimmy
select Z[].Integers[1];
10
where the [2] and [1] refer to the ordinal position of the item (value)
in the list.
But sql doesn't have a table 'type' or a list type so it couldn't
understand these things. The only thing sql can do is try to simulate
these types. And it winds up in a mess of gooblygook that few understand
and robs the user of the integrity that the database should provide
by supporting these things directly. All this stuff is based on values,
variables and types. The only types that exist is sql are numbers and
strings. Again there is no type for tables, row or list. And therefore
no variables for these things because without a type you can't define
a variable. Make sense ☺ .
var X:= Orders //X is a variable of type table, specifically the type
               //of the Orders table (the column names and there data types).
This is the huge step forward from sql. In sql the result of a query
is no different than the 'Constant' that guy was trying to talk about ☺ .
It is also the difference between a CTE and a variable that holds the
result of the 'CTE'. You can't reuse a CTE in sql because it's a 'constant',
a value. You can only reuse a variable.

Dataphor - Merging strings together

This problem was raised in the thread:
microsoft.public.sqlserver.programming
Monday, October 15, 2007 
'Parse and Merge two fields'
http://tinyurl.com/2eosa3

The idea is to take two pipe ('|') delimited strings and created one
string where each item in the first string is matched with the item 
in the second string having the same ordinal position.
The two columns are People and Position. So given:

People:=' Sam | Jane | Gene'
Position:= 'Accounting | Finance | Marketing'

we want:

People_Position:= 'Sam|Accounting|Jane|Finance|Gene|Accounting'

Of course in sql this is usually a mess since sql does not
support a list type and any of the operations that can transform
a delimited string to a list and visa versa.
In D4 we simply split the delimited string to a list of items and
then create a table from the list. The table will contain the
ordinal position as an integer value of each item in the list. 
So a row has the item (string) value and sequence value of that item
in the string as columns. So we just concatenate the columns of each
string together (separated by a pipe) and then concatenate these 
strings over the rows with the Concat operator ordering the 
concatenated string by the sequence value (over rows). 

Note that there is no concept of just unique item in a list. A list
can have any number of duplicate items. When the list is transformed
to a table the sequence number guarantees that same value items will
be in the table. In other words the sequence is a key of the table.

select ToTable('A|A|B|B|C'.Split({'|'}));

value sequence 
----- -------- 
A     0        
A     1        
B     2        
B     3        
C     4      

And with column names of your choice:

select ToTable('A|A|B|B|C'.Split({'|'}),'Str','Seq');

Str Seq 
--- --- 
A   0   
A   1   
B   2   
B   3   
C   4   

(Browse this site for other articles on string operations for more info).

Here is some sample data. The data is stored in a Sql Server 2005 database.
All queries are written with the D4 relational language of Dataphor.

create table JR
{
 id:Integer,
 People:String tags{Storage.Length='100'},
 Position:String tags{Storage.Length='100'},
 key{id}
};
 
insert
table
{
row{1 id,'joe|sam|pete|mike' People,'Manager|Team Lead|Worker Ant|Worker Ant' Position},
row{2,'A| C|  T ',' JobA|JobC |JobT '},
row{3,'A| C ',' JobA | JobB |JobC '}, //Ummatched strings (lists)
row{4,'Bush | Cheney | Rice ' ,' Worser|Worst|Worse '},
row{5,'Z |Y |X ' ,' Backwards Z|Backwards Y |Backwards X '}
} into JR;

select JR;

id People                Position                                
-- --------------------- --------------------------------------- 
1  joe|sam|pete|mike     Manager|Team Lead|Worker Ant|Worker Ant 
2  A| C|  T               JobA|JobC |JobT                        
3  A| C                   JobA | JobB |JobC                      
4  Bush | Cheney | Rice   Worser|Worst|Worse                     
5  Z |Y |X                Backwards Z|Backwards Y |Backwards X

Also note that we can access the table with a pass-thru query using t-sql:

select SQLQuery("SELECT id,People,Position FROM JR"); 

id People                Position                                
-- --------------------- --------------------------------------- 
1  joe|sam|pete|mike     Manager|Team Lead|Worker Ant|Worker Ant 
2  A| C|  T               JobA|JobC |JobCT                       
3  A| C                   JobA | JobB |JobC                      
4  Bush | Cheney | Rice   Worser|Worst|Worse                     
5  Z |Y |X                Backwards Z|Backwards Y |Backwards X   

Note that the only restriction used here is that both strings have
the same number of items. 

select 
  JR //The table JR in Sql Server 2005. 
  //SQLQuery("SELECT id,People,Position FROM JR") Or we could use this t-sql query . 
     where //The same 'where' statement as in sql. 
           //Check that each string has the same number of items.      
          People.Split({'|'}).Count()=Position.Split({'|'}).Count()
                                with {IgnoreUnsupported = 'true'}
      //For each row in the table we 'add' a column (People_Position).
      //The People_Position column is the '|' delimited string of
      //People and Position item by item.
      add
      {
       //From a table whose rows represents each item in People and Position,
       //for each row form a string of the person and position separated by
       //a '|' and concatenate that string over the rows into a single string.
       Concat({People_Position,Del} 
        from
         (
          //Use Split to create a 'list' of items from the delimited string.
          //Then create a table from list with ToTable. Column 'Str1'
          //is the string value of the item from the list. Column 'Seq'
          //is the item number from the list. So now a table represents
          //the original input column People. In other words, the rows of
          //the table are the delimited items in the string from left to right.
          (ToTable(People.Split({'|'}),'Str1','Seq'){Str1.Trim() Str1,Seq} )       
           join  //A natural join on Seq which is an integer from 0-># of items
                 //in the string. Seq preserves the sequence from left to
                 //to right of the items in the string.
          (ToTable(Position.Split({'|'}),'Str2','Seq'){Str2.Trim() Str2,Seq} )
            {Seq,Str1,Str2,'|' Del,(Str1+'|'+Str2) People_Position}  
         )
                order by {Seq}) //The concatenation over rows is ordered by the
                                //Seq column. This is the same order as the items
                                //in the original input columns, People/Position.
                                //Therefore the original order is preserved in
                                //the result (People_Position).
        People_Position //This is name of the new column added to each row of JR.
       }
        {id,People_Position}  //These are the only two columns we've chosen
                              //to display from the query.
            order by {id};    //Order by the values if the id column.
 
id People_Position                                           
-- --------------------------------------------------------- 
1  joe|Manager|sam|Team Lead|pete|Worker Ant|mike|Worker Ant 
2  A|JobA|C|JobC|T|JobT                                      
4  Bush|Worser|Cheney|Worst|Rice|Worse                       
5  Z|Backwards Z|Y|Backwards Y|X|Backwards X                


Instead of eliminating rows where the item counts don't agree
we can insert the string 'Unmatched' for those id values.

select 
 //Instead of table JR we access the table in sql server with a t-sql query.
 //In D4 the query does not return an sql 'resultset' but instead is treated
 //as a table 'variable' which is of a nature fundamentally different than sql.
 SQLQuery("SELECT id,People,Position FROM JR") 
 //Store the restriction on count as a boolean value (T/F) in column TestCnt.
  add{People.Split({'|'}).Count()=Position.Split({'|'}).Count() TestCnt}
                                with {IgnoreUnsupported = 'true'}
   add
      {
 //If the TestCnt is true concatenate the rows, else return a nil (null) value.
 //A case statement just like the sql case statement could also be used.
  if TestCnt
      then
       Concat({People_Position,Del}
        from
         (
          (ToTable(People.Split({'|'}),'Str1','Seq'){Str1.Trim() Str1,Seq} )       
           join
          (ToTable(Position.Split({'|'}),'Str2','Seq'){Str2.Trim() Str2,Seq} )
            {Seq,Str1,Str2,'|' Del,(Str1+'|'+Str2) People_Position}  
         )
                order by {Seq})
                else
                  nil
                    as String
        People_Position
      }
 //Insert the unmatched string for a nil value of the People_Position column.     
        {id,IfNil(People_Position,'*** Unmatched Strings ***') Field} 
          order by {id};
          
id Field                                                     
-- --------------------------------------------------------- 
1  joe|Manager|sam|Team Lead|pete|Worker Ant|mike|Worker Ant 
2  A|JobA|C|JobC|T|JobT                                      
3  *** Unmatched Strings ***                                 
4  Bush|Worser|Cheney|Worst|Rice|Worse                       
5  Z|Backwards Z|Y|Backwards Y|X|Backwards X                 


D4 allows all the expressive power you need to easily test for
any criteria. Here we add some additional rows to table JR that
should be eliminated.

insert
table
{
//Digits in Postion.
row{6 id,'joe|sam|pete|mike' People,'Manager|Team Lead|Worker Ant1|Worker Ant2' Position},
//Digits in People and Position
row{7 id,'joe|sam|pete1|mike' People,'Manager|Team Lead|Worker Ant1|Worker Ant' Position}
} into JR;

select JR;

id People                Position                                  
-- --------------------- ----------------------------------------- 
1  joe|sam|pete|mike     Manager|Team Lead|Worker Ant|Worker Ant   
2  A| C|  T               JobA|JobC |JobT                          
3  A| C                   JobA | JobB |JobC                        
4  Bush | Cheney | Rice   Worser|Worst|Worse                       
5  Z |Y |X                Backwards Z|Backwards Y |Backwards X     
6  joe|sam|pete|mike     Manager|Team Lead|Worker Ant1|Worker Ant2 
7  joe|sam|pete1|mike    Manager|Team Lead|Worker Ant1|Worker Ant

We add some additional restrictions to the where statement to eliminate
rows with strings that contain any non letters.

select 
 JR
  where 
    (
     ( People.Split({'|'}).Count()=Position.Split({'|'}).Count() )
     //We also test that each item is only letters (no digits or other junk).
     //This can be done any numbers of ways. Here we just compare the count
     //of all items to the count of items eliminated by characters other than 
     //letters.
       and
       (
        People.Split({'|'}).Count()
        =
        Count(ToTable(People.Split({'|'}),'F1Str','F1Seq') 
        //Test that items are made up of letters only. To eliminate
        //blanks contaiminating the test we remove blank character before
        //testing.
          where IsLetter(Replace(Trim(F1Str),' ','')))
       )
       and
       (
        Position.Split({'|'}).Count()
        =
        Count(ToTable(Position.Split({'|'}),'F2Str','F2Seq') 
          where IsLetter(Replace(Trim(F2Str),' ','')))
       )   
     )  with {IgnoreUnsupported = 'true'}        
      add
      {
       Concat({People_Position,Del}
        from
         (
          (ToTable(People.Split({'|'}),'Str1','Seq'){Str1.Trim() Str1,Seq} )       
           join
          (ToTable(Position.Split({'|'}),'Str2','Seq'){Str2.Trim() Str2,Seq} )
            {Seq,Str1,Str2,'|' Del,(Str1+'|'+Str2) People_Position}  
         )
                order by {Seq})
        People_Position
       }
        {id,People_Position}
          order by {id};

id People_Position                                           
-- --------------------------------------------------------- 
1  joe|Manager|sam|Team Lead|pete|Worker Ant|mike|Worker Ant 
2  A|JobA|C|JobC|T|JobT                                      
4  Bush|Worser|Cheney|Worst|Rice|Worse                       
5  Z|Backwards Z|Y|Backwards Y|X|Backwards X                

Thanks for stopping by ☺ .

Tuesday, October 09, 2007

Dataphor - Exploding hierarchical data

This article show examples of using the D4 explode operator.
This operator is used for expressing hierarchical data. It is
not a 'recursive' operator like the recursive sql CTE but more
like the Oracle Connect By construct.

The examples follow the ones used by Itzik Ben-Gan to illustrate
the recursive CTE query in Sql Server 2005, specifically the
'Single-Parent Environment: Employees Organizational Chart'
which can be found at:
http://msdn2.microsoft.com/en-us/library/ms345144.aspx#docum_topic4

The explode examples are based on the Employees table used in the
sql example. The table is stored in an Sql Server 2005 database.
Several of the examples use the concept of a dense rank.

As with most of my articles, the code is not necessarily 'clever' but
straightforward, not necessarily the best 'performant' but intended
to express the many different concepts and constructs in D4.

select Employees

empid mgrid empname  salary    
----- ----- -------- ----------
1     0     Nancy    $10,000.00
2     1     Andrew   $5,000.00 
3     1     Janet    $5,000.00 
4     1     Margaret $5,000.00 
5     2     Steven   $2,500.00 
6     2     Michael  $2,500.00 
7     3     Robert   $2,500.00 
8     3     Laura    $2,500.00 
9     3     Ann      $2,500.00 
10    4     Ina      $2,500.00 
11    7     David    $2,000.00 
12    7     Ron      $2,000.00 
13    7     Dan      $2,000.00 
14    11    James    $1,500.00 


Get a tree for a specific manager.

select
 (
  Employees
    explode
   by mgrid = parent empid
   where  mgrid=0
     order by {empid}
       include level
   with {IgnoreUnsupported = 'true'}
  )
// Do a little string insert to format the tree.  
    add {'' Temp, level-1 Totalspace}
     add {
          (
           empname.Insert(0,Temp.PadLeft(Totalspace,'|'))
          ).Replace('|',' | ')
          Tree
         }
        {Tree,mgrid,empid,empname,sequence,level};
       
Tree              mgrid empid empname  sequence level
----------------- ----- ----- -------- -------- -----
Nancy             0     1     Nancy    1        1    
 | Andrew         1     2     Andrew   2        2    
 |  | Steven      2     5     Steven   3        3    
 |  | Michael     2     6     Michael  4        3    
 | Janet          1     3     Janet    5        2    
 |  | Robert      3     7     Robert   6        3    
 |  |  | David    7     11    David    7        4    
 |  |  |  | James 11    14    James    8        5    
 |  |  | Ron      7     12    Ron      9        4    
 |  |  | Dan      7     13    Dan      10       4    
 |  | Laura       3     8     Laura    11       3    
 |  | Ann         3     9     Ann      12       3    
 | Margaret       1     4     Margaret 13       2    
 |  | Ina         4     10    Ina      14       3    

Treat each employee as if they are a manager. This will return the
subordinates of each employee regardless of whether or not
they are a manager.

select
 (
  Employees
    explode
   by mgrid = parent empid
   where  mgrid>=0         //Changing where to include all employees.
     order by {empid}
       include level
   with {IgnoreUnsupported = 'true'}
  )
     add {'' Temp, level-1 Totalspace}
     add {
          (
           empname.Insert(0,Temp.PadLeft(Totalspace,'|'))
          ).Replace('|',' | ')
          Tree
         }
        {Tree,mgrid,empid,empname,sequence,level} ;
       
Tree              mgrid empid empname  sequence level
----------------- ----- ----- -------- -------- -----
Nancy             0     1     Nancy    1        1    
 | Andrew         1     2     Andrew   2        2    
 |  | Steven      2     5     Steven   3        3    
 |  | Michael     2     6     Michael  4        3    
 | Janet          1     3     Janet    5        2    
 |  | Robert      3     7     Robert   6        3    
 |  |  | David    7     11    David    7        4    
 |  |  |  | James 11    14    James    8        5    
 |  |  | Ron      7     12    Ron      9        4    
 |  |  | Dan      7     13    Dan      10       4    
 |  | Laura       3     8     Laura    11       3    
 |  | Ann         3     9     Ann      12       3    
 | Margaret       1     4     Margaret 13       2    
 |  | Ina         4     10    Ina      14       3    
Andrew            1     2     Andrew   15       1    
 | Steven         2     5     Steven   16       2    
 | Michael        2     6     Michael  17       2    
Janet             1     3     Janet    18       1    
 | Robert         3     7     Robert   19       2    
 |  | David       7     11    David    20       3    
 |  |  | James    11    14    James    21       4    
 |  | Ron         7     12    Ron      22       3    
 |  | Dan         7     13    Dan      23       3    
 | Laura          3     8     Laura    24       2    
 | Ann            3     9     Ann      25       2    
Margaret          1     4     Margaret 26       1    
 | Ina            4     10    Ina      27       2    
Steven            2     5     Steven   28       1    
Michael           2     6     Michael  29       1    
Robert            3     7     Robert   30       1    
 | David          7     11    David    31       2    
 |  | James       11    14    James    32       3    
 | Ron            7     12    Ron      33       2    
 | Dan            7     13    Dan      34       2    
Laura             3     8     Laura    35       1    
Ann               3     9     Ann      36       1    
Ina               4     10    Ina      37       1    
David             7     11    David    38       1    
 | James          11    14    James    39       2    
Ron               7     12    Ron      40       1    
Dan               7     13    Dan      41       1    
James             11    14    James    42       1            

If we reverse 'by mgrid = parent empid' to 'by empid = parent mgrid' we
get the tree of a particular employee to their top level manager.

select
 (
  Employees
    explode
      by empid = parent mgrid
         where  empid=13
   order by {empid}
       include level
   with {IgnoreUnsupported = 'true'}
  )
     add {'' Temp, level-1 Totalspace}
     add {
          (
           empname.Insert(0,Temp.PadLeft(Totalspace,'|'))
          ).Replace('|',' | ')
          Tree
         }
        {Tree,mgrid,empid,empname,sequence,level};
       
Tree           mgrid empid empname sequence level
-------------- ----- ----- ------- -------- -----
Dan            7     13    Dan     1        1    
 | Robert      3     7     Robert  2        2    
 |  | Janet    1     3     Janet   3        3    
 |  |  | Nancy 0     1     Nancy   4        4    
       
By changing the where predicate to >=1 we can get a report on all
employees (note where empid>=3 eliminates the graph of the 1st 2
employees (Nancy and Andrew) but doesn't eliminate them from graphs
of other employees who report to them).


Here we create an operator that will give a graph in either direction
of a specific employee and their top level manager. In other words,
we can either start with the employee and go down to their top level
manager or start with the employees top level manager and go down
to the employee. The concept of the dense rank is used for binding
all rows of each employee together. By getting the empname and empid
for each dense rank we can target any employee by name or number.   
(Note we could, of course, use a view or any number of other constructs.
I just felt in the mood to use an operator  ).

The operator takes two arguments. The first, aTreeTable, is a table
of type Employees. The second, Start_At, is a string and indicates
the direction of the graph. Using 'M' for manager starts with the
manager. Using 'E' starts with the employee.

create operator EmpTree(aTreeTable:typeof(Employees),Start_At:String):
//The operator returns a virtual table with columns and their data types
//defined by the typeof expression.
typeof(
       Employees
       add{1 level,'S' empnamerank,1 empidrank, 'T' Tree_Graph, 1 Rank, 1 TreeOrder}
       )
begin
result:=table of typeof(result){};
//Starting at top level mgr for each employee is a desc sort (default).
//Starting at each employee to the top level mgr. is an asc sort.    
//Start_At=M(gr) is desc sort (default)
//Start_At='E(mp) is asc sort.
var LSort:='D'; //Default.
if ( ((Start_At.Trim())[0]).Upper() ) = 'E'
   then LSort:='A' ;
var T:=
aTreeTable
explode  
by empid = parent mgrid
    where  empid>=1
      order by {empid}
       include level
   with {IgnoreUnsupported = 'true'};
//Get a dense rank. This rank binds all rows for each employee
//together. The idea is to increment a count for every level 1 since
//a level 1 indicates the start of a new employee.
var TR:=
       T add{case when level=1 then empname else nil end NameEmp}
        add
           {
            Count(
                  T rename {sequence sequenceX}
                   where  (sequenceX<=sequence) and (level=1)
                  ) 
             Rank
            };
var SR:=
     TR               
      join
       (
        TR group by {Rank}
        //We want the emp name, number (empid) and reverse level for each
        //dense rank (empname). We want the reverse level so we can get
        //the tree representative from the employee to top level manager
        //AND the top level manager to the employee.
                add{Max(empid) NumEmpid,Max(NameEmp) Emp_Name,Max(level) Maxlevel}
        )                        
 add {
    (empname.Insert(0,''.PadLeft( (if LSort='A' then (level-1) else (Maxlevel-level)),
                                                         '|'))).Replace('|',' | ')
        Tree_Graph
     }
     rename {sequence seq};
result:=
 ToTable(
  ToList(
 cursor(SR
 order by
     {
      Rank,
      seq
      sort ((1 - (2*ToInteger((LSort = 'D'))))*(.left.value ?= .right.value)) asc
     }
    
       )//cursor
       )//ToList
      )  //ToTable
        {empid,mgrid,empname,salary,level,Emp_Name empnamerank,
          NumEmpid empidrank,Tree_Graph,Rank,sequence+1 TreeOrder};
end;         


Here we show the first five employees by using Rank in a where statement.
The value of the Rank corresponds to the ascending order of empid.
Because empid starts at 1 Rank happens to be equal to the empid.

select EmpTree(Employees,'Mgr') //We use the operator as if it were a table.
  where Rank<=5
      order by {TreeOrder};     

empid mgrid empname  salary     level empnamerank empidrank Tree_Graph   Rank TreeOrder
----- ----- -------- ---------- ----- ----------- --------- ------------ ---- ---------
1     0     Nancy    $10,000.00 1     Nancy       1         Nancy        1    1        
1     0     Nancy    $10,000.00 2     Andrew      2         Nancy        2    2        
2     1     Andrew   $5,000.00  1     Andrew      2          | Andrew    2    3        
1     0     Nancy    $10,000.00 2     Janet       3         Nancy        3    4        
3     1     Janet    $5,000.00  1     Janet       3          | Janet     3    5        
1     0     Nancy    $10,000.00 2     Margaret    4         Nancy        4    6        
4     1     Margaret $5,000.00  1     Margaret    4          | Margaret  4    7        
1     0     Nancy    $10,000.00 3     Steven      5         Nancy        5    8        
2     1     Andrew   $5,000.00  2     Steven      5          | Andrew    5    9        
5     2     Steven   $2,500.00  1     Steven      5          |  | Steven 5    10     


We can overload the EmpTree operator so as to provide a default value
for the Start_At parameter, ie. the direction of the graph. We make
the default 'M' so the graph starts with the top level manager of the
employee.

We simply supply the literal 'Mgr' for the sort direction for the
same operator whose signiture includes the Start_At parameter.

create operator EmpTree(aTreeTable:typeof(Employees)):
typeof(
       Employees
       add{1 level,'S' empnamerank,1 empidrank, 'T' Tree_Graph, 1 Rank, 1 TreeOrder}
       )
begin
result:= EmpTree(aTreeTable,'Mgr');
end;

     
Here get the tree of employee 'James' starting at the highest level manager
by using the overload signature of the EmpTree operator.

select EmpTree(Employees)
  where empnamerank='James'
  {Tree_Graph,TreeOrder}
      order by {TreeOrder};
     
Tree_Graph        TreeOrder
----------------- ---------
Nancy             38       
 | Janet          39       
 |  | Robert      40       
 |  |  | David    41       
 |  |  |  | James 42       


Here we start with employee 'James' up to his highest level manager.

select EmpTree(Employees,' Emp ')
  where empnamerank='James'
  {Tree_Graph,TreeOrder}
      order by {TreeOrder};
     
Tree_Graph        TreeOrder
----------------- ---------
James             38       
 | David          39       
 |  | Robert      40       
 |  |  | Janet    41       
 |  |  |  | Nancy 42        


Here we use the EmpTree operator to get the enumerated paths in both
directions using the Concat (concatenation) operator. (More info here).

select
 (EmpTree(Employees) add{'.' Del} adorn{key{empidrank,TreeOrder}})
   group by {empidrank}
    add 
          {
            Max(empnamerank) empname,
            Concat(empname,Del order by {empidrank,TreeOrder}) PathMgrtoEmp,
            Concat(empname,Del order by {empidrank,TreeOrder desc}) PathEmptoMgr
          }
     rename {empidrank empid}
      order by {empid};
     
empid empname  PathMgrtoEmp                   PathEmptoMgr                  
----- -------- ------------------------------ ------------------------------
1     Nancy    Nancy                          Nancy                         
2     Andrew   Nancy.Andrew                   Andrew.Nancy                  
3     Janet    Nancy.Janet                    Janet.Nancy                   
4     Margaret Nancy.Margaret                 Margaret.Nancy                
5     Steven   Nancy.Andrew.Steven            Steven.Andrew.Nancy           
6     Michael  Nancy.Andrew.Michael           Michael.Andrew.Nancy          
7     Robert   Nancy.Janet.Robert             Robert.Janet.Nancy            
8     Laura    Nancy.Janet.Laura              Laura.Janet.Nancy             
9     Ann      Nancy.Janet.Ann                Ann.Janet.Nancy               
10    Ina      Nancy.Margaret.Ina             Ina.Margaret.Nancy            
11    David    Nancy.Janet.Robert.David       David.Robert.Janet.Nancy      
12    Ron      Nancy.Janet.Robert.Ron         Ron.Robert.Janet.Nancy        
13    Dan      Nancy.Janet.Robert.Dan         Dan.Robert.Janet.Nancy        
14    James    Nancy.Janet.Robert.David.James James.David.Robert.Janet.Nancy
    

Here we get the counts of employees directly or indirectly reporting to managers.

select
  Employees
    explode
      by empid = parent mgrid
         where  empid>=1
   order by {empid}
       include level
   with {IgnoreUnsupported = 'true'}
     group by {mgrid} add{Count() MgrCnt}
      where mgrid>0

 mgrid MgrCnt
 ----- ------
 1     13    
 2     2     
 3     7     
 4     1     
 7     4     
11     1    


Here are the employees who are the managers. For example there are 13 people
who report to Nancy. This is represented indirectly by people who report
to the three managers directly below her (Andrew, Janet, Margaret) and
directly by the same managers reporting to her. Dan, David and Ron report
to Robert. But there is an additional employee reporting to David (James)
so Robert (mgrid 7) has 4 employees reporting to him.

select
 (
  Employees
    explode
      by empid = parent mgrid
         where  empid>=1
   order by {empid}
       include level
   with {IgnoreUnsupported = 'true'}
     group by {mgrid} add{Count() MgrCnt}
  )
   join Employees
     where mgrid>0
      join  ( Employees {empid MgrEmpid,empname MgrName} )
        by mgrid=MgrEmpid
      {mgrid,MgrName,empname,MgrCnt};
     
mgrid MgrName  empname  MgrCnt
----- -------- -------- ------
1     Nancy    Andrew   13    
1     Nancy    Janet    13    
1     Nancy    Margaret 13    
2     Andrew   Michael  2     
2     Andrew   Steven   2     
3     Janet    Ann      7     
3     Janet    Laura    7     
3     Janet    Robert   7     
4     Margaret Ina      1     
7     Robert   Dan      4     
7     Robert   David    4     
7     Robert   Ron      4     
11    David    James    1           


Here we get salaries of subordinates under managers. Those employees
who are not managers are omitted.

var T:=
Employees
explode  
by mgrid = parent empid
     where  mgrid>=0
      order by {empid}
       include level
   with {IgnoreUnsupported = 'true'};
//Get a dense rank. This rank binds all rows for each employee
//together. The idea is to increment a count for every level 1 since
//a level 1 indicates the start of a new employee.
var TR:=
       T add{case when level=1 then empname else nil end NameEmp}
        add
           {
            Count(
                  T rename {sequence sequenceX}
                   where  (sequenceX<=sequence) and (level=1)
                  ) 
             Rank
            };
var SR:=
   (
     TR               
      join
       (
        TR group by {Rank}
        //We want the empid and name for each dense rank.
          add{Min(empid) NumEmpid,Max(NameEmp) Emp_Name}
        )                        
    )
    //We only want managers, those that have subordinates.
     where
     NumEmpid
              in
                ( Employees {mgrid} ) with {IgnoreUnsupported = 'true'}
      add {
         ( (empname+ case when level>1 then ' ('+ToString(salary)+')'
             else '' end).Insert(0,''.PadLeft((level-1),'|'))).Replace('|',' | ')
              Mgr_Sal_Tree
          };

select SR {Mgr_Sal_Tree,sequence} order by {sequence};   

Mgr_Sal_Tree                  sequence
----------------------------- --------
Nancy                         1       
 | Andrew ($5,000.00)         2       
 |  | Steven ($2,500.00)      3       
 |  | Michael ($2,500.00)     4       
 | Janet ($5,000.00)          5       
 |  | Robert ($2,500.00)      6       
 |  |  | David ($2,000.00)    7       
 |  |  |  | James ($1,500.00) 8       
 |  |  | Ron ($2,000.00)      9       
 |  |  | Dan ($2,000.00)      10      
 |  | Laura ($2,500.00)       11      
 |  | Ann ($2,500.00)         12      
 | Margaret ($5,000.00)       13      
 |  | Ina ($2,500.00)         14      
Andrew                        15      
 | Steven ($2,500.00)         16      
 | Michael ($2,500.00)        17      
Janet                         18      
 | Robert ($2,500.00)         19      
 |  | David ($2,000.00)       20      
 |  |  | James ($1,500.00)    21      
 |  | Ron ($2,000.00)         22      
 |  | Dan ($2,000.00)         23      
 | Laura ($2,500.00)          24      
 | Ann ($2,500.00)            25      
Margaret                      26      
 | Ina ($2,500.00)            27      
Robert                        30      
 | David ($2,000.00)          31      
 |  | James ($1,500.00)       32      
 | Ron ($2,000.00)            33      
 | Dan ($2,000.00)            34      
David                         38      
 | James ($1,500.00)          39    

Here is the same tree as above (of salaries for subordinates) using a
table and a view created with a pass-thru query.

We can create a table based on the result of explode.

create table Emp_T
from
     (
       Employees
       explode  
       by mgrid = parent empid
       where  mgrid>=0
       order by {empid}
       include level
       with {IgnoreUnsupported = 'true'}
       add{case when level=1 then empname else nil end NameEmp}
        adorn  //We can include various meta-data about the columns of the table.
             {
              NameEmp nil static tags {Storage.Length = "10"},
              empname static tags {Storage.Length = "10"}
             }//end adorn.
     );  

Now we get the dense rank using a pass-thru query to Sql Server. The result
of the sql query could be set to var TR which means that TR is a table
variable in D4. The TR variable is therefore of the same nature
no matter how it (a table variable) was derived. Or we could create
a view based on the pass-thru query and use that.
var TR:= but we're using a view instead of setting the pass-thru to var TR.

create view TR
  SQLQuery("select A.*, 
      (select Count(*)
              from Emp_T as B
                 where (B.sequence<=A.sequence) and (B.level=1)) as Rank
          from Emp_T as A");

Use the TR view in a batch to get the salary tree.

var SR:=
   (
     TR               
      join
       (
        TR group by {Rank}
        //We want the empid and name for each dense rank.
                add{Min(empid) NumEmpid,Max(NameEmp) Emp_Name}
        )                        
    )
    //We only want managers, those that have subordinates.
     where
     NumEmpid
              in
                ( Employees {mgrid} ) with {IgnoreUnsupported = 'true'}
      add {
         ( (empname+ case when level>1 then ' ('+ToString(salary)+')'
             else '' end).Insert(0,''.PadLeft((level-1),'|'))).Replace('|',' | ')
              Mgr_Sal_Tree
          };
select SR {Mgr_Sal_Tree,sequence} order by {sequence};

Mgr_Sal_Tree             sequence
------------------------ --------
Nancy                    1       
 | Andrew (5000)         2       
 |  | Steven (2500)      3       
 |  | Michael (2500)     4       
 | Janet (5000)          5       
 |  | Robert (2500)      6       
 |  |  | David (2000)    7       
 |  |  |  | James (1500) 8       
 |  |  | Ron (2000)      9       
 |  |  | Dan (2000)      10      
 |  | Laura (2500)       11      
 |  | Ann (2500)         12      
 | Margaret (5000)       13      
 |  | Ina (2500)         14      
Andrew                   15      
 | Steven (2500)         16      
 | Michael (2500)        17      
Janet                    18      
 | Robert (2500)         19      
 |  | David (2000)       20      
 |  |  | James (1500)    21      
 |  | Ron (2000)         22      
 |  | Dan (2000)         23      
 | Laura (2500)          24      
 | Ann (2500)            25      
Margaret                 26      
 | Ina (2500)            27      
Robert                   30      
 | David (2000)          31      
 |  | James (1500)       32      
 | Ron (2000)            33      
 | Dan (2000)            34      
David                    38      
 | James (1500)          39


Here we get the sum of salaries for subordinates under managers using
the TR view. Eliminating level 1 in the query excludes the managers
salary in the sum. Again the dense rank idea makes this an easy query.

select
   (
    TR               
      join //This is a natural join based on Rank.
       (
        TR group by {Rank}
        //We want the empid and name for each dense rank.
            add{Min(empid) NumEmpid,Max(NameEmp) Emp_Name}
        )//->The relation of view TR being group by Rank.
    )//->The relation from TR joined to (TR grouped by Rank) 
          where level>1 //A 'where' applied to above relation. This relation
                        //is now grouped by NumEmpid to get subordinate salaries.
            group by{NumEmpid} add{Max(Emp_Name) Emp_Name,Sum(salary) SumSalary}
              order by {NumEmpid};

NumEmpid Emp_Name SumSalary 
-------- -------- ----------
1        Nancy    37500.0000
2        Andrew   5000.0000 
3        Janet    15000.0000
4        Margaret 2500.0000 
7        Robert   7500.0000 
11       David    1500.0000 

Monday, October 01, 2007

Sql - The Two Standards of the Sql CTE

Sql really has two standards. There is the ANSI sql standard and then
there is the DOUBLE standard.

This example uses Sql Server 2005 and specifically the CTE (common table expression).

Sql Server sees nothing wrong with this query that duplicates a column name.
Something that no database should allow.

SELECT employeeid,customerid as employeeid
FROM orders

From Bol on the CTE column_name
'Duplicate names within a single CTE definition are not allowed.The list of
 column names is optional only if distinct names for all resulting  columns
 are supplied in the query definition.'

But this query produces an error.

WITH Emps  (Emp, Emp) AS
(
    SELECT employeeid,customerid as employeeid
    FROM orders
)
SELECT *
FROM Emps
Error: 'The column 'Emp' was specified multiple times for 'Emps'.

But try this. And it works!

WITH Emps  (Emp, Emp1) AS
(
    SELECT employeeid,customerid as employeeid
    FROM orders
)
SELECT *
FROM Emps

So what does Bol really mean in the column_name description. It means
that the stupid and error prone idea of creating duplicate column names
is perfectly ok in the query definition. It's only the outer reference
names (the  ( column_name [ ,...n ] ) that are going to be checked for
duplicates. So the MS statement:
'The list of column names is optional only if distinct names for all
 resulting columns are supplied in the query definition.'

is a recognition that its okay to screw up the query_definition as
as long as you come away with distinct names in the CTE. In other words,
give distinct names for the 'same' columns created in the query_definition
with the same name. If you don't duplicate names in the quey_definition
you can dispense with the CTE column name list totally. So not only is the
MS statement a statement of guilt (don't expect us to correct duplicate column
names when it first occurs, we'll catch it the 2nd time it comes around),
it's also a statement of a double standard. One standard for an independent
SELECT statement and another standard for a CTE! Double standards allow
the initial error to propagate. Not what you want in a database.

This is what a database should do when duplicate columns are declared.
This is what Dataphor will do when you try this.

select Orders {EmployeeID,CustomerID EmployeeID};
Error: Duplicate object name "EmployeeID".

Only if you 'love' sql could you tolerate its nonsense ☺ .

For more on the sql CTE see:
'Do you know what the Sql CTE is?'
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html

Sql - Do you know what the Sql CTE is?

From Sql Server 2005 Bol for WITH CTE (common_table_expression):
'Specifies a temporary named result set, known as a common table expression (CTE).
 This is derived from a simple query and defined within the execution scope of
 a single SELECT, INSERT, UPDATE,
 or DELETE statement.'

Now this works fine (using the NorthWind database in Sql Server 2005):

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

But try to define a CTE independently. You can't.
So this CTE craps out:

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)

Try to use the CTE in a batch with a 2nd SELECT. You can't 'reuse' the CTE.
So this batch craps out with an error thrown on the use of the CTE
on the 2nd SELECT.

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

SELECT Min(Cnt)
FROM Emps       -- Invalid object name 'Emps'.

Now what if a CTE in a batch could be defined independently and
be used with multiple SELECT statements. What would the batch look
like. Well it could look something like this using the D4 language of Dataphor
(see http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
 for some more background):

//Define a CTE independently. And to define a CTE independently it must
//be stored in a variable. We are using a pass-thru query from D4 to Sql Server
//in t-sql to the NorthWind database.
var EMPS.CTE:=
              SQLQuery("SELECT employeeid,count(*) AS Cnt
                          FROM orders
                            GROUP BY employeeid");
//Define a row using the CTE.
var LRow:=row{Min(Cnt from EMPS.CTE) MinCnt,Max(Cnt from EMPS.CTE) MaxCnt};//A row.  
//SELECT the row.
select LRow; 
/*
MinCnt MaxCnt
------ ------
42     156
*/
//Use the CTE in a 2nd SELECT, SELECT a table
select EMPS.CTE return 2 by {Cnt desc};
/*
employeeid Cnt
---------- ---
4          156
3          127
*/

What is really going on here. Now Sql has managed to immunize itself
against computer science forever. But if we were to apply a compute
science term to the sql CTE what term what we call it. We would call
it a value. Because a value cannot exist independently (by itself :))
and cannot be reused. To reuse a value we have to declare every time
we want to use it. With a variable we don't have to worry about
any of these value limitations. We can simply reuse the variable
as much as we want. The 'var' in 'var EMPS.CTE:=..' means define
a variable that contains a value and the value is the CTE SELECT
statement. So now you know the sql term 'result set' is really just
a value :) And now you know the benefit of working with variables
over values.

Of course the situation with the sql CTE is not the only case where
you have to repeat the damn thing to use it.

This works fine in sql.

SELECT orderid,customerid,employeeid
FROM orders
WHERE customerid in ('COMMI','TORTU','HUNGO')

But can you store the list of ('COMMI','TORTU','HUNGO') in a variable? No.
You have to repeat it every time you want to use it. It's because there is
no variable available to assign the list to. Of course in D4 we can
assign the list to a variable and use the variable just like the CTE.

var LList:=list(String){'COMMI','TORTU','HUNGO'};
select Orders
where CustomerID in LList
{OrderID,CustomerID,EmployeeID};

Doesn't this make more sense than having to use xml in sql to shred the
string or other crazy sql techniques to store the list items in a table? 

About Me

My photo
Phoenix, Arizona, United States