Drawing with DB2 for i SQL – Happy New Year 2022!

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