Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP1)
Database: MonetDB v11.17.11 (Jan2014-SP1), 'test'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE "sys"."d10" (
more>        "idkey"   INTEGER,
more>        "v0601"   VARCHAR(255),
more>        "pes_wgt" DOUBLE,
more>        "v0001"   VARCHAR(255)
more>);
operation successful (10.364ms)
sql>COPY 20635472 RECORDS INTO d10 FROM 's:/temp/example.txt';
20635472 affected rows (22.9s)
sql>create table XYZ as (select idkey, v0601 from d10) with data;
operation successful (1.7s)
sql>create unique index XYZ_INDEX on XYZ(idkey);
operation successful (1.1s)
sql>create table ABC as (select idkey , (1*(v0601='1')) as v06011 , (1*(v0601='2
')) as v06012 from XYZ) with data;
operation successful (1.1s)
sql>create unique index ABC_INDEX on ABC(idkey);
operation successful (773.775ms)
sql>select
more>   sum( pes_wgt * v06011 ),
more>   sum( pes_wgt * v06012 ),
more>   v0001
more>from
more>   d10 inner join ABC using(idkey)
more>where
more>   pes_wgt<>0
more>group by
more>   v0001
more>order by
more>   v0001;
+--------------------------+--------------------------+-------+
| L2                       | L3                       | v0001 |
+==========================+==========================+=======+
[[snip]]
+--------------------------+--------------------------+-------+
27 tuples (5.2s)
sql>