Last Updated on 31 December 2021 by Roberto De Pedrini
Of course one must have time to waste to spend the whole afternoon trying to draw, with recursive SQL queries, Christmas trees, other amenities, the last day of the year (Today 31 December 2021)
Actually, just yesterday I took the third shot of Anti-Covid vaccine and in the morning I spent it sleeping …
After lunch I try to do something to distract me, to try not to raise the fever again, and I go to the computer.
My dog (Ugo) looks at me from the window not too happy for this choice on a day that feels like Spring with 20 degrees centigrade in the middle of the mountains (thanks to the Favonio – Phoen wind).
Let’s say it was meant to be a nice way to wish Happy New Year 2022 to the entire IBM i Community … given my absence of these periods for “super-work” and various changes.
Before delighting you with nice little trees, let’s try to understand the concepts of SQL Recursive Query
Recursive CTE SQL – Create a series of numbers
Here is an example of a recursive query (actually we can also avoid the “recursive” keyword, I put it just for clarity).
Anyone who has played with BOMs or other tables with recursive dependencies will also have used the “Connect By” keywords (we talked about it in this post (A bit of cross join: https://blog.faq400.com/it/database-db2-for-i/cross-join-db2-for-i/ ).
WITH recursive take10(list_of_numbers) AS
(SELECT 0 FROM sysibm.sysdummy1
UNION ALL
SELECT
list_of_numbers+1
FROM take10
WHERE list_of_numbers < 10)
SELECT * FROM take10;
A Christmas tree
Now that we understand recursive queries, let’s have fun … creating a small “Christmas tree”
— Small Tree WITH small_tree (tree_depth,— Small Tree
WITH small_tree(tree_depth,pine) AS (
SELECT 1 tree_depth,rpad(‘ ‘,10,’ ‘) || ‘*’ pine
FROM sysibm.sysdummy1
UNION ALL
SELECT small_tree.tree_depth +1 tree_depth,
rpad(‘ ‘,10-small_tree.tree_depth,’ ‘) ||
rpad(‘*’,small_tree.tree_depth+1,’.’) ||
lpad(‘*’,small_tree.tree_depth,’.’) pine
FROM small_tree
where small_tree.tree_depth < 10
)
SELECT pine
FROM small_tree;
My dog probably did it better too … but I’ve never had any artistic ambitions.
Three Christmas trees
We can also exaggerate …. these trees don’t cost that much and are absolutely environmentally friendly!
WITH small_tree(tree_depth,pine) AS (
SELECT 1 tree_depth,
rpad(‘ ‘,10,’ ‘) || ‘*’
|| rpad(‘ ‘,20,’ ‘) || ‘*’
|| rpad(‘ ‘,20,’ ‘) || ‘*’
pine
FROM sysibm.sysdummy1
UNION ALL
SELECT small_tree.tree_depth +1 tree_depth,
rpad(‘ ‘,10-small_tree.tree_depth,’ ‘) ||
rpad(‘*’,small_tree.tree_depth+1,’.’) ||
lpad(‘*’,small_tree.tree_depth,’.’) ||
rpad(‘ ‘,20-small_tree.tree_depth-tree_depth,’ ‘) ||
rpad(‘*’,small_tree.tree_depth+1,’.’) ||
lpad(‘*’,small_tree.tree_depth,’.’) ||
rpad(‘ ‘,20-small_tree.tree_depth-tree_depth,’ ‘) ||
rpad(‘*’,small_tree.tree_depth+1,’.’) ||
lpad(‘*’,small_tree.tree_depth,’.’) pine
FROM small_tree
where small_tree.tree_depth < 10
)
SELECT rpad(‘ ‘,9,’ ‘) ||’Ho’
|| rpad(‘ ‘,19,’ ‘) || ‘Ho’
|| rpad(‘ ‘,19,’ ‘) || ‘Ho’
pine
FROM sysibm.sysdummy1
UNION ALL
SELECT pine
FROM small_tree;
The man with the hat
The Man with the Hat … maybe it was also a movie … or a book … or simply the typical man with a hat on his small car who cuts your way at 20 Miles per Hour when you are in a hurry.
Here it is!
– A man with a Hat (https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/)
WITH RECURSIVE hat (hat_pattern, level)
AS (
SELECT
CAST(REPEAT(‘ ‘, 11) concat trim(‘/V ‘) AS VARCHAR(100))
AS hat_pattern,
1 AS level
from sysibm.sysdummy1
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 10-level) || ‘/’
|| REPEAT(‘V’, 2 * level + 1) || trim(‘V ‘)
AS VARCHAR(100))
AS repeated_pattern,
hat.level + 1
FROM hat
WHERE level < 6
)
SELECT hat_pattern
FROM hat
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 5) || ‘|’ || ‘ ‘ || ‘|’
AS VARCHAR(100))
AS forehead
from sysibm.sysdummy1
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 5) || ‘|’ || ‘ O / O ‘ || ‘|’
AS VARCHAR(100))
AS eyes
from sysibm.sysdummy1
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 5) || ‘|’ || ‘ /_ ‘ || ‘|’
AS VARCHAR(100))
AS nose
from sysibm.sysdummy1
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 5) || ‘|’ || ‘ ~~~~~ ‘ || ‘|’
AS VARCHAR(100))
AS mouth
from sysibm.sysdummy1
UNION ALL
SELECT
CAST(
REPEAT(‘ ‘, 5) || ‘|’ || ‘ { | } ‘ || ‘|’
AS VARCHAR(100))
AS chin
from sysibm.sysdummy1;
We also turn on some lights … randomly!
Here is another tree with Christmas lights that “turn on” randomly (try throwing it several times)!
Here must be an effect of the Vaccine that makes me see the @ as if they were tree lights!
select replace(
replace(
replace(mystring, ‘X’ , ‘O’)
, ‘T’, ‘u’)
, ‘@’, ‘i’)
from
(
;
select * from (
select lpad(‘ ‘,20-e-i) ||
case when rand() < 0.3
then substr(s,1,e*2-3+i*2)
else substr(substr(s , 1 ,int(rand()*(e*2-3+i*2-1))) || ‘@’ || s , 1 ,e*2-3+i*2) end as “ChristmasTree”
from
( select rpad(‘X’,40,’X’) s,
rpad(‘T’,40,’T’) t
from sysibm.sysdummy1 ) ,
( select level i, level+2 hop from sysibm.sysdummy1 connect by level <= 4 ) , lateral
(select level e
from sysibm.sysdummy1
connect by level <= hop )
union all
select lpad(‘ ‘,17)||substr(t,1,3) from
(select rpad(‘X’,40,’X’) s,
rpad(‘T’,40,’T’) t
from sysibm.sysdummy1)
connect by level <= 5
) a ;
Conclusions
I hope I have at least stolen a smile from you … I don’t think this “post” brings much value to your work (but have a laugh every now and then!)
They say that the best gifts are the ones where one invests one’s time to make them … not limiting itself to the simple gesture of taking out the credit card from the wallet (or directly from the Smartphone): here I have invested a whole post-vaccination afternoon.
I wish the IBM i Community a good year 2022, which is better in all respects than the past one!
Best wishes for a happy 2022 from all the Faq400 staff!
PS One thing is certain … this 2022 will be a year of important changes and new things boiling in the pot …. we will update you soon!
Roberto
Links and references
In reality there is very little of mine in this post … I just readjusted some SQL material from other DBs for our big DB2 for i SQL … but don’t tell anyone!
How to Draw a Christmas Tree in SQL https://learnsql.com/blog/draw-christmas-tree-sql/SQL Server Recursive CTE: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/Print Christmas tree in SQL Server: https://sqlskull.com/2020/12/02/print-christmas-tree-in-sql-server/How to printout a Christmass Tree in SQL: https://stackoverflow.com/questions/65434426/how-do-i-print-out-a-christmas-tree-with-sql
—
Roberto De Pedrini
Faq400.com
