select '~#'|| firstnme||
       '~#'|| lastname||
       '~#'|| char(edlevel)|| 
       '~#'|| case edlevel when 16 then 'system engineer'
                           when 18 then 'senior engineer'
                           else         'unkonwn'
              end      as string
 from em

ORDER BY 1

WITH EVAL_TABLE(BUS_CHANNEL, DIVISION_NODE, DIVISION_NODE_NM, OWN_RESP_NODE_CD, NODE_NM, SRC_SYS_ACT_NO) AS (# SELECT
# CASE
#   WHEN SYS1.S1_CL_MG_ACT_D.OWN_RESP_NODE_CD IN
#     ('D2192', 'D3155', 'D3211', 'D3212', 'D3213', 'D3214',
#      'D3215', 'D3216',

create table customer_purchases(customer_id   char(3) not null,
                                purchase_date date    not null,
                                vehicle_id    char(4) not null);
commit;
insert into customer_purchases values('C1', '2000-02-29', 'VIN1');
insert into customer_purchases values('C1', '2000-05-26', 'VIN2');
insert into customer_purchases

-- using first 3 joins as an example
create table join_is(h_num int not null, ph_ts timestamp not null);
create table ad_ph(h_num int not null, ts timestamp not null, cmd_cd varchar(3) not null);
create table ad_com(cmd_cd varchar(3) not null, ts timestamp not null, v_frm int not null);

insert into join_is values(0, timestamp('2009-01-01 00:00:00'));

create table dept_sum(deptno,
                      employees,
                      total_salary) as
       (select workdept, count(*), sum(salary)
          from colin.employee
         group by workdept)
       data initially

create table testmdc as (select empno, dept from colin.empmdc) data initially deferred refresh deferred

create table A(seq int, id char(1));
insert into A values (1,'A');
insert into A values (2,'B');
insert into A values (3,'C');

create table B(seq int, order int);
insert into B values (1,1);
insert into B values (1,2);
insert into B values

select empno,
              max(email) as email,
              max(mgremail) as mgremail
     from (select empno, email , ' ' as mgremail
                   from emp_t
                 union all
                 select empno, ' ' as email, mgremail
                    from mgr_t) x
   group by empno

select rtrim(T.tabschema)||'.'||rtrim(T.tabname) as table_name
  from syscat.tables T 
  with ur