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!