Dataphor - Sql: Table This!

Recently a learned user of MS Sql Server pointed out to me
that MS supports something they call a 'table variable' which
is of type table.

It is defined here.

Now MS does indeed say:
'variables can be declared to be of type table'
'To declare variables of type table, use DECLARE @local_variable.'

Then they go on to add a real zinger:
'Assignment operation between table variables is not supported.'

Talk about having your cake and wanting to eat it!
This is nothing but bs:) A variable can only be of type table
IF it supports assignment operations. If you can't put two
tables around an equality (=) and have the compiler understand
that is a comparison between two tables, you don't have a
table type. D4 (as I will be referring to the dataphor relational
language from now on) makes explicit comparisons between tables
as shown here.

Also note that there is no such thing in Sql Server as a table 
type. Permanent tables, temporary tables and table valued functions
are subject to the same argument as table variables. In fact
Sql Server would have to require every thing they call a table
to at least have a key, let alone support assignment operations,
before they could even think of saying they support a table type.

This is just another example of D4 vs. sql or TITS vs. FITS :)

Dataphor - a Formal Defintion

A formal defintion of Dataphor can be found here.

Simply put Dataphor represents a new declarative solution
to application development. At the heart of dataphor is
data management based on a true implementation of the
Relational Model. Dataphor builds on this relational foundation
to provide various application development services such as
the presentation layer. Dataphor therefore represents a new
paradigm for development called automated application
development. And is the next step in the evolution of
application development technology beyond current rapid
application development or RAD. 

The relational model in dataphor is based on the work of
E. F. Codd and continued by C. J. Date.

The data management language used in dataphor is formally
called D4. It is a relational language embedded in an 
imperative language. The relational component is based
on the relational language put forth by C. J. Date and
Hugh Darwen called Tutorial D in:

'Databases, Types and the Relational Model (3rd Edition)'
which can be found here.

The imperative part of D4 is based on the Pascal language.

In other words, in D4 the statement:

if x=y then
<do something>
else
<do something else>

is as appropriate for integers, as it is for strings
as it is for tables!

Unlike sql systems, the dataphor compiler is a relational
inference engine. In faithfully following the relational
model, keys are inferred for newly derived tables. Whereas
sql systems emphasis keys to physically access tables,
dataphor uses keys to logically access tables. Such
emphasis on the relational logical model allows operations
on tables that are not supported in sql. Updatability of
views of almost any complexity and the ability to declare
constraints of any complexity are but two examples.

Dataphor is also a derivation engine. At the presentation
layer forms are derived from the logical definition of tables
and from user declared metadata. It is in this sense that
dataphor uses a declarative method for application development.

Dataphor uses a device to store and access data. In other
words data resides in a repository accessed by dataphor.
A device can be any sql system such as MS Sql Server,
Oracle, DB2 etc. If desired the device can be accessed directly
thru D4 by the dialect of sql it supports. Data created
directly on an sql system can be easily made available
to dataphor. In this sense you can have the best of both
worlds:)

The following quote from the Dataphor Help docs succinctly
conveys Dataphors new paradigm for development, the inherent
limitations historically built into sql and how they are
overcome with the relational model in the context of an
imperative language in a declarative application development
environment.
 
'SQL was designed as a 'database sublanguage.' It was
intended to be used from 3GL programming environments as
a means of retrieving and manipulating data in the database.
As such, it had no facilities for imperative programming.
This implies that application development is done in one
language, while data manipulation is done in another. 
This disparity of language environments is called
impedance mismatch. D4 is really just an imperative
programming language where one of the types of data that
can be described is a table. As a result, applications
can be developed entirely in D4, with no resulting
impedance mismatch.'

I will be concentrating on D4 as it manipulates data in a
relational model as opposed to sql data management.

I hope you find it inspirational and uplifting:)

Dataphor - TITS vs. FITS

Catchy title, huh:)
Ok, one more metaphor before we get down to the nitty gritty.

Dataphor is what I call a TITS:
a Tight Information Technology System 

Tight as in reasoned and concise. Tight as in having full control
over the things it manipulates.  Tight as in logic and mathematics
where there exists no ambiguities. Tight as in algebra where one
thing can be inferred from another.

Sql is what I call a FITS:
a Funky Information Technology System

Funky as in something that functions, but in a slightly strange,
klugey way. Funky as in it does the job and would be difficult to
change, so its obvious non-optimality is left alone. As in the
more bugs something has that nobody has bothered to fix because
workarounds are easier, the funkier it is.

All the loose ends, ambiguities and redundancies of sql are
over come in dataphor. All the twilight of sql becomes clarified
in dataphor. All the gotcha's in sql become tight in dataphor.

I believe you can teach old dogs new tricks.
And I hope I can turn a funky programmer into a tight one.
So if you're tired of throwing sql fits meet a great system
based on TITS.

RAC - Sealed with a KISS

Here's a recent question posted in an MS Sql Server newsgroup:

I am trying to formulate a SELECT statement to query the following
table:

CREATE TABLE dbo.PART_TABLE(
  PART_ID VARCHAR(12) NOT NULL
, JOB_NUMBER VARCHAR(12) NOT NULL
, ENTRY_DATE DATETIME NOT NULL DEFAULT GETDATE()
, TYPE_CODE VARCHAR(12) NOT NULL
, PRIMARY KEY (PART_ID, JOB_NUMBER, ENTRY_DATE)
)


What I want to do is to return all rows of the table where the
TYPE_CODE is the same for two consecutive ENTRY_DATEs, for each
PART_ID.

The PART_IDs will not necessarily have a daily entry, so the
'consecutive' dates may actually be a few days apart.

In other words, I want to know the rows where a PART_ID had the same
TYPE_CODE on the chronologically previous ENTRY_DATE as the next.

So, if my table contained:

-------------------------------------------------
1111       30       01/06/06      ZXY
2222       40       01/06/06      ZXY
2222       35       03/06/06      ABC
1111       23       03/06/06      ZXY
3333       87       02/06/06      ABC
---------------------------------------------------

the query would return:
1111       30       01/06/06      ZXY
1111       23       03/06/06      ZXY

as the same TYPE_CODE was used on two consecutive ENTRY_DATEs for
PART_ID '1111'.

A number of very bright sql programmers proposed solutions.
Unfortunately you would need to be an extremely sophisticated
sql expert to understand them. There is a point at which the 
code completely obfuscates the logic of the solution.
Now I'm an advocate of KISS (keep it simple stupid). This was
one of the major motivations for developing the RAC utility.
Questions like the one above can be kissed very easily if
framed properly. At its simplist this problem can be solved
by sorting and computing a few ranks based on the sort.

Here is some same data:

insert PART_TABLE values('1111',30,'01/06/06','ZXY')
insert PART_TABLE values('2222',40,'01/06/06','ZXY')
insert PART_TABLE values('2222',35,'03/06/06','ABC')
insert PART_TABLE values('1111',23,'03/06/06','ZXY')
insert PART_TABLE values('3333',87,'02/06/06','ABC')
insert PART_TABLE values('1111',21,'03/10/06','ZXY')
insert PART_TABLE values('2222',23,'03/21/06','ABC')
insert PART_TABLE values('3333',24,'02/16/06','HUH')
insert PART_TABLE values('2222',27,'01/04/06','ABC')

Here is the data sorted in RAC without any other processing.
Sorting by the sequence of PART_ID, ENTRY_CODE and TYPE_CODE
encapsulates the whole solution.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE & TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER 
------- --------- ---------- ---------- 
1111    ZXY       01/06/06   30
1111    ZXY       03/06/06   23
1111    ZXY       03/10/06   21
2222    ABC       01/04/06   27
2222    ZXY       01/06/06   40
2222    ABC       03/06/06   35
2222    ABC       03/21/06   23
3333    ABC       02/06/06   87
3333    HUH       02/16/06   24

Now we're going to put a counter/rank on 
TYPE_CODE within PART_ID called TYPERANK.
Every time PART_ID changes or TYPE_CODE within
PART_ID changes the rank is set back to 1.
Otherwise the rank increments by 1. We're also
going to add a first/last indicator for the rank
called LASTYPE. If the rank is not the last combination
of PART_ID/TYPE_CODE it's 0, if its the last a 1.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
1111    ZXY       03/10/06   21         3        1
2222    ABC       01/04/06   27         1        1
2222    ZXY       01/06/06   40         1        1
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1
3333    ABC       02/06/06   87         1        1
3333    HUH       02/16/06   24         1        1

Now using TYPERANK and LASTYPE it's easy to pick off
the 1st 2 consecutive rows for PART_ID and TYPE_CODE.
The @wherecounters parameter makes this simple test.
Conceptually all this is done with a single pass thru
the data.

Exec Rac
@transform='_dummy_',
@rows='PART_ID & TYPE_CODE & ENTRY_DATE(date) & JOB_NUMBER',
@rowsort='PART_ID & ENTRY_DATE &  TYPE_CODE',  
@pvtcol='Report Mode',
@from='PART_TABLE',@rowbreak='n',@defaults1='y',
@defaultexceptions='dumy',@racheck='y',
@rowcounters='TYPE_CODE{TYPERANK}',
@lastcounters='TYPE_CODE{LASTYPE}',
@wherecounters='LASTYPE=0 or TYPERANK=2'

PART_ID TYPE_CODE ENTRY_DATE JOB_NUMBER TYPERANK LASTYPE 
------- --------- ---------- ---------- -------- ------- 
1111    ZXY       01/06/06   30         1        0
1111    ZXY       03/06/06   23         2        0
2222    ABC       03/06/06   35         1        0
2222    ABC       03/21/06   23         2        1

Kiss/RAC which also stands for less is more.

Hopefully this has been helpful. Or are we back to 'huh'?:)

Dataphor - Mixed Metaphors

What do you call bright people who become experts 
in a silly exercise? Like bright people who become
expert sql programmers. They are the ultimate hair
stylists. They spend so much time and brain power
splitting hairs that we actually feel sorry for them
and reward their persistance with the title of 'expert':) 
Just like we rewarded the guys who spent a year building
the ship model 'inside' the glass bottle. They were the
experts until someone came along who showed that it was
so much easier to build the model first and then glue
each side of glass around the ship. Viola, ship in a
bottle. Dataphor is the new way to build a ship in a
bottle. And you can build a much better ship since you
don't have to worry about a glass ceiling. 

Dataphor - Solving the Humpty Dumpty Problem

Humpty Dumpty sat on a wall,
Humpty Dumpty took a big fall,
All the kings horses and all the kings men,
Couldn't put Humpty together again.

It seems people are always running into the same problem
with concatenating strings in sql. But we can put Humpty
together very easily in Dataphor.

Lets concatenate the strings that we created in the banana split
post. First we'll create a table and insert the split strings.
This is the familiar sql operation of insert/select.

create table exampleconcat
 { ID      : Integer,
   index   : Integer,
   strpart : String,
   key {ID,index}
 } 

insert
  (
   examplesplit times numbers       
    where Split(STRING,{ ':',',','/' } ).Count()>DIGIT
     add {Split(STRING,{ ':',',','/' } ).Count() cnt,
          Split(STRING,{ ':',',','/' } )[DIGIT].Trim() strpart}
          {strpart,DIGIT+1 index,ID}     
   )   
     into exampleconcat

But wait a minute will ya. Notice there's no 'select'. That's because
in sql 'select' is part of all expressions. In Dataphor it's only a
statement. Not only is its meaning logical, it;s intuitive. In Dataphor you
you can order 'select' a la carte, not so in sql:) This is a BIG deal
as you shall see as we get more involved with queries.

An even BIGGER deal is that Dataphor inserts data independent of ordinal
position. It's only concerned with matching the names of the columns.
The columns to be inserted specified by:

{strpart,DIGIT+1 index,ID} 

could be scrambled any way you want. The order has no relationalship to
the sequence of columns declared in table 'exampleconcat'.In all the
places that sql requires taking into account the order of columns, you
needn't concern yourself about it in Dataphor.

select exampleconcat          
ID index strpart
-- ----- -------
1  1     AA     
1  2     BB     
1  3     CC     
1  4     DD     
1  5     EE     
1  6     FF     
1  7     GG     
2  1     KK     
2  2     JJ     
2  3     II     
2  4     HH     
2  5     AA     
2  6     BB     
3  1     LL     
3  2     MM     
3  3     NN     
3  4     PP     
4  1     QQQ    
4  2     RRRR  

To concatenate the strings for each ID is childs play:) The concat
operator is like an aggregate function,ie. count,min,max etc. You
group by ID and add the concat operator. It takes 3 arguments,the
string column, a delimiter and an order by column for how you want
the result ordered.

select
 exampleconcat 
  add {',' Delimiter}
   group by {ID}
    add { Concat(strpart,Delimiter order by {index}) concatstr }
   
ID concatstr           
-- --------------------
1  AA,BB,CC,DD,EE,FF,GG
2  KK,JJ,II,HH,AA,BB   
3  LL,MM,NN,PP         
4  QQQ,RRRR        

Are you impressed?:)

Before I let you go a few words about add. Yeah, it's a BIG deal:)

Consider this. Lets create a table of 1 row and a single column X
with a value of 5.

select ToTable({5},'X')

X
-
5

Now add another column to the table, Y.

select ToTable({5},'X') add {6 Y}

X Y
- -
5 6

Now add the expression X*Y.

select ToTable({5},'X') add {6 Y} add {X*Y XY}

X Y XY
- - --
5 6 30

Now add an expression involving XY and Y.

select ToTable({5},'X') add {6 Y} add {X*Y XY} add {XY-Y Z}

X Y XY Z 
- - -- --
5 6 30 24

See a pattern here? Remember high school algebra?:) Well this is algebra
with tables! You know the problem that sql has with using expressions
(aliases) within the same select? Gone. The often repeated sql question:
'why can't I use an alias defined in 'select' in the 'where' statement?'

select ToTable({5},'X') add {6 Y} add {X*Y XY} add {XY-Y Z}
where Z>20

Gone. Fini. Case closed:)

Compare this to t-sql.

select X,Y,XY,XY-Y Z
from
 (select X,Y,X*Y XY
from
  (select 5 as X,6 as Y) t1
                       ) t2
                      
You have to select each expression before you can use it in another
expression. Give me a break:) What is the mathematical concept that
describes this sql behavior? Mess, from the latin idea of messi?:)
Did someone ask about subqueries? You guessed it, you add subqueries
just like any other expression. And another post. And yes I could
talk about the sql need of table aliases (t1,t2) but I feel
magnanimous now so I won't beat a dead horse:)

Hang with me for just a few more seconds I want to show you
something really interesting. Lets suppose in our example we want
ID's with a string count of greater than 4. First we'll add the
Count() aggregate to the concat aggregate. We'll call the count 'cnt'.

select
 exampleconcat 
  add {',' Delimiter}
   group by {ID}
    add { Concat(strpart,Delimiter order by {index}) concatstr,Count() cnt }

ID concatstr            cnt
-- -------------------- ---
1  AA,BB,CC,DD,EE,FF,GG 7  
2  KK,JJ,II,HH,AA,BB    6  
3  LL,MM,NN,PP          4  
4  QQQ,RRRR             2

To get the ID's with a count greater than 4 you would use 'having' in
sql right? As in 'having count()>4'. This gets to the fact that in
sql there are 2 different statements that perform the same conceptual
thing ie. a restriction. And the one you use depends on whether your
grouping ('having') or operating on non grouped data ('where'). The
usual reason given when newbies ask about this is 'thats the way
it works in sql'. Good thing the designers of this sql mess didn't
go into medicine:)
Now in Dataphor after the 2nd add we have a table. How do you
restrict the rows of a table? Yep with where. One where fits
all(situations):) There is no logical reason for 'with this use that,
with that use this'. So it's as simple as this:

select
 exampleconcat 
  add {',' Delimiter}
   group by {ID}
    add { Concat(strpart,Delimiter order by {index}) concatstr,Count() cnt }
     where cnt>4
      order by {cnt desc} 
     
ID concatstr            cnt
-- -------------------- ---
1  AA,BB,CC,DD,EE,FF,GG 7  
2  KK,JJ,II,HH,AA,BB    6

Since we're we using an algebra for tables we can add parenthesis
around the statements that make up the table we're applying where to:

select
 (
   exampleconcat 
    add {',' Delimiter}
     group by {ID}
      add { Concat(strpart,Delimiter order by {index}) concatstr,Count() cnt }
  )    
       where cnt>4
        order by {cnt desc}

Ok, thanks for sticking around. There's much more to come!

Dataphor - a Banana Split

A common problem in sql is to split a delimited string without
using cursors or any type of procedural code. Talk about how
to have fun!:) And to spice it up, how about multiple delimiters.
Ok here's an example of splitting a string over rows with multiple
delimiters, colon(:), comma(,) and slash(/). The column 'STRING'
is what we want to tear apart over 'ID'. You will love this!

// Keywords are in bold.
select 
table
 {
 row {1 ID, 'AA: BB, CC/ DD, EE/ FF: GG' STRING},
 row {2,'KK, JJ: II, HH, AA: BB'},
 row {3,'LL : MM , NN / PP'},
 row {4,'QQQ / RRRR'},
 key {ID}
 }
  times 
   ToTable({0,1,2,3,4,5,6,7,8,9,10},'DIGIT')     
    where STRING.Split( { ':',',','/' } ).Count()>DIGIT
     add {STRING.Split( { ':',',','/' } ).Count() cnt,
          STRING.Split( { ':',',','/' } )[DIGIT].Trim() strpart}
          {ID,strpart,DIGIT+1 index,cnt} 
        order by {ID,index }

ID strpart index cnt
-- ------- ----- ---
1  AA      1     7
1  BB      2     7
1  CC      3     7
1  DD      4     7
1  EE      5     7
1  FF      6     7
1  GG      7     7
2  KK      1     6
2  JJ      2     6
2  II      3     6
2  HH      4     6
2  AA      5     6
2  BB      6     6
3  LL      1     4
3  MM      2     4
3  NN      3     4
3  PP      4     4
4  QQQ     1     2
4  RRRR    2     2

Lets go over this. I promise it won't take long, it's that simple.
First we'll get over the clutter of the virtual tables and create
some permanent ones. First we'll create table 'examplesplit'.

create table examplesplit
from
 table
 {
 row {1 ID, 'AA: BB, CC/ DD, EE/ FF: GG' STRING },
 row {2,'KK, JJ: II, HH, AA: BB'},
 row {3,'LL : MM , NN / PP'},
 row {4,'QQQ / RRRR'},
 key {ID}
 }
  adorn  STRING tags {Storage.Length = "100"}}

select examplesplit
ID STRING                 
-- --------------------------
1  AA: BB, CC/ DD, EE/ FF: GG
2  KK, JJ: II, HH, AA: BB 
3  LL : MM , NN / PP      
4  QQQ / RRRR

Don't worry about adorn, I'll come back to that in due time.
Now create a table of digits called 'numbers' with a column 'DIGIT'.

create table numbers
from
 ToTable({0,1,2,3,4,5,6,7,8,9,10},'DIGIT')

select numbers
DIGIT
-----
0 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10

Now here we go with 2 versions of the new query to grap hold of
of you regardless of your inclination:)

select 
 examplesplit times numbers    
    where STRING.Split( { ':', ',','/' } ).Count()>DIGIT
     add {STRING.Split( { ':', ',','/' } ).Count() cnt,
          STRING.Split( { ':', ',','/' } )[DIGIT].Trim() strpart}
          {ID,strpart,DIGIT+1 index,cnt} 
       order by {ID,index}
select 
 examplesplit times numbers    
    where Split(STRING,{ ':',',','/' } ).Count()>DIGIT
     add {Split(STRING,{ ':',',','/' } ).Count() cnt,
          Split(STRING,{ ':',',','/' } )[DIGIT].Trim() strpart}
          {ID,strpart,DIGIT+1 index,cnt} 
       order by {ID,index}

ID strpart index cnt
-- ------- ----- ---
1  AA      1     7
1  BB      2     7
1  CC      3     7
1  DD      4     7
1  EE      5     7
1  FF      6     7
1  GG      7     7
etc.

The 'STRING.Split' is for those that are inclined to believe in
UFO's (objects) and their modus operandi of abductions (methods).
The 'Split(STRING' is for those that have a history of renditions
at the hands of sql.

Now 'examplesplit times numbers' is like sql's 'cross join'. Now
comes the kewl part. The Spit thingie splits a string so easily
because it consumes (as in eats up) the string as a 'list'. This
is a BIG deal. When is a door not a door? When it's a jar:) When is
a 'string' more than itself, when it's a 'list':) So with knowledge
of the delimiters Split is operating on a 'list'. If you have a list
you should know how many items are in it. And you do with Count():

STRING.Split( { ':',',','/' } ).Count()
Split(STRING,{ ':',',','/' } ).Count()

Lists are 0 based. So for ID 1 there are 7 strings in the list.
The elements in the list are therefore 0-6. So the where restriction
forms rows for each ID that have DIGIT less than the Count(). And
DIGIT can be used as an index into the list to return a particular
string, ie. 'strpart'. So to the table formed by times and where, for
each row we add the Count() as column 'cnt' and a particular string 
as column 'strpart' by using DIGIT in either of the expressions:

STRING.Split( { ':',',','/' } )[DIGIT].Trim() strpart
Split(STRING,{ ':',',','/' } )[DIGIT].Trim() strpart

You will see that this [] thingie, called an indexer, can be used not
only on lists but also tables whatever form they take. This construct
is crazy kewl and I'll show just how kewl and powerful it is as we
work our way thru Dataphor. Trust me:)

The expression surrounded by the swigglies:

{ID,strpart,DIGIT+1 index,cnt}

is used to return the columns you want and also to define new columns
which is what 'index' is.

By the way did it register that this is exactly the opposite of sql!
In sql you state what you want returned before you even tell it what
to process. Here we process stuff and after we're finished we can
pick and choose what we want. In sql it's like going to a resturant
and asking for the check before you've eaten let alone even ordered!
Waitress:'Welcome to Alice's Restaurant sir'. Sqler:'Hi, check please'.
Waitress thinking:'what a nitwit':) This is a big deal in writing
queries as you will see.

And did you notice that there is no 'from' in 'select'! It's not there
because you don't need it! It's redundant. Why do you have to write
'from' and the table. Just stating the table name should be sufficient.
Writing both is like paying for the same piece of real estate twice.
Come on, this isn't brain surgury it's just common sense and logical.
And Dataphor is filled with both:)

As for order by...well guess:)

I hope your enthused about Dataphors banana split compared to the
splitting headache in sql:)

Stay tuned:)

First Sql now Dataphor

Yeah, come on all of you, big bright men,
Data management needs your help again.
He's got himself in a terrible jam
Way down yonder in sql land
So put down your server and pick up some rum,
We're gonna have a whole lotta fun.

To paraphrase William F. Buckley Jr.'s quote from Austrian Willi Schlamm:
The trouble with sql is sql.
The trouble with database management is sql.

I'm an advocate of something wonder-filled called 'Dataphor' (www.alphora.com)
If your into database management, data modeling and sql programming
I have big news for you. So if your working with MS Sql Server, Oracle,
DB2 etc. etc. here's a news flash:
You've have been waiting for Dataphor forever but you just don't know it!

Take a gander at these 'select' statements:

Dataphor supports a table 'type'. So you can represent a
table in a 'select' statement using keywords that define
a table, ie. row, key and of course table. 

select
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}

ID ID2 stringvalue
-- --- -----------
1  10  AA
1  20  BB
1  30  DD
1  40  DD
1  50  EE
2  60  DD
2  70  AA
2  80  EE
2  90  BB
2 100  BB
2 110  FF

A rose is a rose is a rose. Tables in Dataphor are
'always' a table, ie. a row is always unique and
the table has a key. If a key isn't specified all
the columns of the table will make up the key.
Therefore there is no DISTINCT keyword in Dataphor.
This is a 'BIG,BIG' deal as you will see.

select
(
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}
) over {ID,stringvalue}

The above query returns only ID and stringvalue columns using over.
Do you see that duplicate rows are eliiminated.
You sent in a real table, you got a real table returned.

ID stringvalue
-- -----------
1 AA
1 BB
1 DD
1 EE
2 AA
2 BB
2 DD
2 EE
2 FF

Now lets return ID by removing the other columns.
Why doesn't sql have a 'remove', especially when you  
want to get rid of one column and you got a bunch.

select
(
table
{ row {1 ID,10 ID2,'AA' stringvalue},
row {1,20,'BB'},
row {1,30,'DD'},
row {1,40,'DD'},
row {1,50,'EE'},
row {2,60,'DD'},
row {2,70,'AA'},
row {2,80,'EE'},
row {2,90,'BB'},
row {2,100,'BB'},
row {2,110,'FF'},
key {ID2}
}
)
remove{ID2,stringvalue}

ID
--
1
2

Get it! :) 
Ok here's a little more nudging. Think of the verb to 'dup', as in
'de-duping' a table using GROUP BY, JOINS, DISTINCT. All that craziness
of getting rid of duplicates is history. Good riddance. And note that
'dup' can also be an adjective:)

Do these predicates look familiar:

=, <>, <, <=, >, >=,

well you can now apply them to tables! Is this a step forward?
What do you think!:)

Next some kewl examples of how to do stuff that usually drives you nuts.

Don't Use RAC

I told you I like bs.Of course you should you use RAC.And not only because it's not free. RAC is a utility for MS Sql Server.Check it out at: www.rac4sql.net RAC was way ahead of its time and remains so:) I find the people against RAC more interesting than those who use it.One of the major criticisms against it is that it undermines the learning curve of the user.By solving pivoting and other messy problems the user will be robbed of an important coding education.Yeah and we're on a noble goal of building a democracy in Iraq!When most users were fumbling around, like a monkey trying to screw a football, attempting to get dynamic crosstabs and do complex ranking along came RAC.Instead of embracing it,as in the meaning of 'utility',it met resistance.And this resistance was the code word 'education'.What it really did was rob a bunch of nitwits from furtile areas of showing their coding ability.RAC was a threat to sql programming newsgroup participation.Ego,ego,ego.Since there doesn't appear to be any bitching about 'administrative' utilities,this would appear to be a case of the classic double standard.One set of rules for administration and other set for data manipulation.Meaning that administration has a lower place in the food chain.This should make DBA's feel good:) I'll revisit this topic later with emphasis on olap/sql-99 and the usual whipping boy of dynamic sql .