db2 SQL: value(), values(), with, recursive SQL

踏雪寻梅

comment:
-- comment should be on a separate line outside an SQL statement

select 1 from (values 1) as aa -- this is not a valid db2 comment


way to create simple tests against dynamic table:
select 'a' as col1,'b' as col2 from table (values 1) as dummy

or shorter:
select 'a','b' from (values 1) qq

In fact, for simple tests you don't even need select and a dummy table.
values ( 'mama', 1)
values (cast (2.5 as decimal(10,3)) * cast (2.5 as decimal(10,3)) )

using cast( ) and values( ), building null values on the fly:
select
  'abc' as col1,
  cast(null as varchar(80)) as col2
from
  table (values (1,2),(3,4)) as dummy

Hint how to make running tests from the prompt easy:
I create 2 aliases:

alias vv='vi test.sql'
alias rr='db2 -tvf test.sql'

Type 'vv' to edit test.sql file (remember - you should end each SQL statement with a semicolon ';')
Then type 'rr' to run this SQL.

simple insert:
insert into inst1.test (A) values ('a'), ('b'), ('c')

using case:
select
  case
    when 1<2 then 'mama'
    when 1>2 then 'papa'
  end
  as person
from table (values 1) as qq

concatenating:
select 'mama' concat ' papa' from table (values 1) as qq;
select 'mama' || ' papa' from table (values 1) as qq;

union, intersect & except:
select .. from T1 union T2 - remove duplicates
select .. from T1 union all T2  -- preserve duplicates
select .. from T1 intersect T2 - remove duplicates
select .. from T1 intersect all T2  -- contains min number of repetition
select .. from T1 except T2 - remove duplicates, then do except
select .. from T1 except all T2 do except, then remove duplicates

value( ) function:
-- value() function - accepts a variable number of parameteres and returns a
first non-null value
-- parameters should be of compatible types
-- the actual name for this function is coalesce() - means "to arise from a
combination of distinct elements"
select value(1,2) from (values 1) as aa
-- returns 1
select value(cast(null as int),2) from (values 1) as aa
-- returns 2
select value(cast(null as int),cast(null as int),3) from (values 1) as aa
-- returns 3

------------------------------------------------------------

2 ways (common table and inline table expression) to define table dynamically in the SQL statement:

with tree (id,pid) as (
    values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  )
select * from tree

        ID      PID
        1       2
        2      3
        3       4
        4       [NULL]
        5       3
        6       5
-------------------------------------------- another way to do the same
select * from (
  values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  )
  as a

Getting root of the tree using recursive SQL:

with

  tree (id,pid) as (
    values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
  ),

  rr (id,pid) as (
    select tr.id, tr.pid from tree tr where tr.id=1
      union all
    select tt.id,tt.pid from tree tt, rr
    where tt.id = rr.pid
  )

select id from rr where rr.pid is null

More recursive SQL:

We have table storing a tree (each row has id and parent_id).
To get root id for a given id (12345) we fill out temporary table rr:
first with given id and its parent_id. Then union with recursively calculated
id and parent_id as we go up the tree:

  with rr (parent_id, id) as
  (
      select p.parent_id, p.id
        from mytree p
       where p.id = 12345
  union all
      select p.parent_id, p.id
        from mytree p, rr
       where p.id = rr.parent_id
  )
  select id ROOT from rr where parent_id is null;

Here is an example going in the oposite direction (down the tree). We showing the whole tree under a given id:

  with rr (id, level) as
  (
      select id, 1
        from mytree
       where id = 10001
  union all
      select child.id, parent.level + 1
        from mytree child, rr parent
       where parent.id = child.parent_id
  )
  select * from rr;

  Result:
           ID         LEVEL
  ----------------------------
         10001            1
         29361            2
         23044            3
         25162            3
         25302            3

Here is how to combine 2 above queries together:

----------------------------------------------------------------------------
  -- Given any node in any tree, this query will drill up to the top
  -- level of the tree, and then query down to give all nodes that exist
  -- within the tree (i.e. given any node_id, show the whole
  -- tree that contains it).
  --
  -- Note: This query will never go deeper than 'stop_level' levels (10).
  ----------------------------------------------------------------------------

  with rec_root (parent_id, child_id, sub_query, level, stop_level) as
  (
      select my_parent_id, my_id, 1, 0, 0
        from mydb.mytable
       where my_id = 25162
  union all
      select parent.my_parent_id, parent.my_id, 2, 1, child.level + 1
        from mydb.mytable parent, rec_root child
       where parent.my_id = child.parent_id
         and sub_query in (1,2)
         and child.stop_level < 10
  union all
       select my_parent_id, my_id, 3, parent3.level + 1, parent3.level + 1
         from rec_root parent3, mydb.mytable child3
        where parent3.child_id = child3.my_parent_id
          and (   (parent3.sub_query = 2 and parent3.parent_id is null)
                or parent3.sub_query = 3
              )
          and parent3.stop_level < 10
  )
  select * from rec_root
   where parent_id is null
      or sub_query = 3;