CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
lalaland
lalaland

Reputation: 341

How to query based on a hierarchy level?

I have table that contains 3 main attributes: STAGING_ID, DEVELOPMENT_ID & PRODUCTION_ID for each NUMBER_ID. I want to query a table based on this table that says:

IF there are NO null values in any of the three columns, select STAGING_ID in 'MAIN_ID' column and 'LEVEL_DESCRIPTION' to be STAGING
IF there is a NULL value in only STAGING_ID (not DEVELOPMENT_ID or PRODUCTION_ID), then select the next level, which would be DEVELOPMENT_ID in 'MAIN_ID' column, and 'LEVEL_DESCRIPTION' to be DEVELOPMENT
IF there is a NULL value in STAGING & DEVELOPMENT (not PRODUCT_ID), then select the next level, which would be PRODUCT_ID in 'MAIN_ID' column, and 'LEVEL_DESCRIPTION' to be PRODUCTION

So the levels always follow this hierarchy, STAGING > DEVELOPMENT > PRODUCTION.

So in the picture,
FIRST ROW, should be: LEVEL_DESCRIPTION: STAGING, MAIN_ID: 3532
SECOND ROW, should be: LEVEL_DESCRIPTION: DEVELOPMENT, MAIN_ID: 3532
THIRD ROW, should be: LEVEL_DESCRIPTION: PRODUCTION, MAIN_ID: 1081

enter image description here

So when I query based on this table, I would need 3 columns:

NUMBER_ID, MAIN_ID, LEVEL_DESCRIPTION

Is there I can do that? I'm thinking of a case statement, but I'm having trouble formulating this.

DROP TABLE TBL_1;
CREATE TABLE TBL_1
(
    number_id int, 
      production_id int,
      develpoment_id int, 
    staging_id int
);

INSERT INTO TBL_1
values 
(11111,2331, 1233, 3532),
(11122,9080, 1291, null),
(11144,1082, null, null)

Upvotes: 2

Views: 65

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

Using COALESCE and CASE:

SELECT *, 
  COALESCE(staging_id, development_id, production_id, 'Not found') AS main_id,
  CASE WHEN staging_id IS NOT NULL THEN 'Staging'
       WHEN development_id IS NOT NULL THEN 'Development'
       WHEN production_id IS NOT NULL THEN 'Production'
       ELSE 'Not found'
  END AS Level_description
FROM TBL_1;

db<>fiddle demo

Upvotes: 2

Related Questions