Selecting and reading data

To read data from a file, you first select the data you want to read using the ddsel verb, which returns a statement handle. You then use ddfet or ddfch to fetch the records.

The left argument of ddsel is a SQL selection expression. Here are typical examples:

Select all records (* means all columns):

   sh=. 'select * from tdata' ddsel ch

Fetch the first 3 records:

   ddfet sh,3
+--------------+-+----+---------+---------+-----+
|MACDONALD B   |F|D101|1.95906e7|1.97805e7|32591|
+--------------+-+----+---------+---------+-----+
|GENEREAUX S   |F|D103|1.94503e7|1.96602e7|95415|
+--------------+-+----+---------+---------+-----+
|KOEBEL R      |M|D101|1.93711e7|1.98009e7|63374|
+--------------+-+----+---------+---------+-----+

Fetch the next record:

   ddfet sh
+--------------+-+----+---------+---------+-----+
|KELLER J      |F|D101|1.95105e7|1.97404e7|48898|
+--------------+-+----+---------+---------+-----+

Close the statement handle:

   ddend sh

You should always close the statement handle when you no longer need it. However to avoid repetition, the remaining examples do not show this.

Select males with salary exceeding 40000:

   sel=.'select * from tdata where sex=''M'' and salary >= 40000'
   sh=. sel ddsel ch
   ddfet sh,4
+--------------+-+----+---------+---------+------+
|KOEBEL R      |M|D101|1.93711e7|1.98009e7|63374 |
+--------------+-+----+---------+---------+------+
|NEWTON R      |M|D108|1.95601e7|1.97902e7|73368 |
+--------------+-+----+---------+---------+------+
|DINGEE S      |M|D103|1.9641e7 |1.98309e7|46877 |
+--------------+-+----+---------+---------+------+
|ROGERSON G    |M|D101|1.95712e7|1.98302e7|108777|
+--------------+-+----+---------+---------+------+

Select only the name, department and salary fields, where date of birth is before 1950:

   sel=.'select name,dept,salary from tdata where dob<19500000'

Fetch the first such record:

   ddfet (sel ddsel 1),1
+--------------+----+-----+
|GENEREAUX S   |D103|95415|
+--------------+----+-----+

Use ddfch to return data in columns:

   [a=. ddfch 1005,_1
+--------------+-+----+---------+---------+-----+
|MACDONALD B   |F|D101|1.95906e7|1.97805e7|32591|
|GORDON E      |F|D103|1.95202e7|1.97908e7|29960|
|BAUERLEIN J   |F|D103|1.96204e7|1.98409e7|33668|
|CHESHER D     |F|D103| 1.9561e7|1.98408e7|35184|
+--------------+-+----+---------+---------+-----+

   (;:'name sex dept dob doh salary')=. a
salary
32591
29960
33668
35184