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
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;
页:
[1]