My Tech Journey

NAGERI — Thu, 29 Feb 2024 13:20:32 +0000

Hey! 👋
I believe as you are reading this interesting blog, you are either in tech or have an interest in the same.
Goal setting and accountability are the two things that have prompted me to give some notes on this tech (technology) journey.

I gained interest in tech way back in 2009. I used to visit my cousin who had a play PlayStation Two (PS2). We played all kinds of games (violent games like GTA5). So I wanted to know more about how the games come to life before knowing how to create one. You have to know about a computer and all that is related. I never knew how to use one leave alone owning one. I went to this guy and asked if he could teach me how to type on a computer, he was a geek already. I remember the question he asked me, "Have you mastered Swahili?" He wanted to know if I scored 100% the previous term. This hindered me but it was the start of a tech journey.

I am guessing you know what happened next! 😂. I started learning how to use the computer later on at campus and I got a good friend, Simon, who introduced me to a beginner boot camp using HTML5, CSS, JavaScript, and Python. I struggled to grasp the content and focused on being a full-stack software engineer.

I am currently doing a boot camp with KanzuCode Foundation Backend track. Thanks to the scholarship I got from Peter a senior Software Engineer. I have learned the art of following. You have to learn to become. The fastest way to go up is copy-paste. looking at the cars, whether Toyota, Mercedes-Benz, Ford, BMW, Honda, and others. They copy from each other. When they introduced the button to open/close the widow. Everyone stopped making cars that have the manual window crank.

In five years, If I diligently continue copying, learning, and innovating. I would have developed cutting-edge tools, leveraging AI, and data science. I will be a consultant and mentor to many other developers who will have results to show not reasons to give.
This is the WHY, the reason I am part of this Backend Bootcamp💪.
It is never too late to start, life long learning has unopposed benefits. Where will you be in five years?

SQL for Data Engineers (oracle SQL)

NAGERI — Fri, 13 Jan 2023 06:38:04 +0000

Aggregating many columns into one column in SQL.

I encountered a problem and I wanted to get the solution the quick and easy way, saving evergy and time, like all engineers out there.
So I came up with this a kindof MVP (replace the P with S for solution), incase of better more optimum solution, please comment and share with the community.

SELECT  signatory_id 
       ,insert_on 
       ,insert_by 
       ,last_updated_on 
       ,last_updated_by 
       ,signatory_user 
       ,signatory_active 
       ,allow_weight_note 
       ,for_bu 
       ,allow_fcr 
       ,allow_whr 
       ,allow_hc
 FROM  signatories_t
      ;

The query above retrives this result, that we are working with tonight.

The goal we want to achieve is grouping the ALLOW_* columns into one called DOCUMENTS separated by a dilimiting character ('/') backslash.

Step 1:
Use the UNION clause, while selecting only one column that you want to aggregate in a CASE clause.

SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_hc = 'Y' THEN 'HC' END  DOCUMENT FROM signatories_t 
UNION 
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_fcr = 'Y' THEN 'FCR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_whr = 'Y' THEN 'WHR' END DOCUMENT FROM 
 signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_weight_note = 'Y' THEN 'WEIGHT_NOTE' END DOCUMENT FROM signatories_t

The above query will give you the below results:

Step 2:
Create an anonymous view (v_view) and SELECT from it the columns that you want and agrregate the document column, by including a GROUP BY clause.

WITH v_view AS (
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_hc = 'Y' THEN 'HC' END  DOCUMENT FROM signatories_t 
UNION 
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_fcr = 'Y' THEN 'FCR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_whr = 'Y' THEN 'WHR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_weight_note = 'Y' THEN 'WEIGHT_NOTE' END DOCUMENT FROM signatories_t 

) SELECT 
      signatory_id
     ,insert_on 
     ,insert_by 
     ,last_updated_on 
     ,last_updated_by 
     ,signatory_user 
     ,for_bu
     ,signatory_active 
     ,listagg(document,'/') within group(order by 1) as document
FROM v_view
GROUP BY signatory_id, insert_on, insert_by, last_updated_on, last_updated_by,signatory_user, for_bu, signatory_active
 ;

NOTE: LISTAGG is one of OracleSQL aggregate functions (return a single result row based on groups of rows, rather than on single rows).
I used SQLDeveloper to access my schema table and run the queries.

DEV Community: NAGERI

My Tech Journey

SQL for Data Engineers (oracle SQL)

Aggregating many columns into one column in SQL.