How to use CASE statement when having multiple hierarchy picks

Database
Enthusiast

How to use CASE statement when having multiple hierarchy picks

Hello Experts,

I am  fairly new to Teradata and need help  to sort my issue.

I have a scenario where one line item has multiple status_codes. there is a hierarchy  preference set for these  status_codes to pick just one.

I have to use a Case statement(i think) to pick the appropriate Status_code for a given line item

Current situation:  there is a one to many relationship between LINE ITEM and STATUS CODE

FIX: I  have to get a ONE to ONE relationship between LINE_ITEM and STATUS_CODE. i.e if one line_item is assigned to multiple codes then pick the highest rank code in hierarchy.

EXAMPLE:

LINE_ITEM          STATUS_CODE

1234                          A

1234                          U

456                            G

1234                           F

567                             A

HIERARCHY given by business:

  Rank          Status_code

  1.             A
  2.             U
  3.             1
  4.             G
  5.             F
  6.             E
  7.             L
  8.             C

I would greatly appreciate for a quick solution.

Thanks,

AM

Tags (1)
2 REPLIES
Senior Apprentice

Re: How to use CASE statement when having multiple hierarchy picks

Put a filter using ROW_NUMBER in a Derived Table and join to it:

SELECT *
FROM tab
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY LINE_ITEM
ORDER BY
CASE STATUS_CODE
WHEN 'A' THEN 1
WHEN 'U' THEN 2
WHEN '1' THEN 3
WHEN 'G' THEN 4
WHEN 'F' THEN 5
WHEN 'E' THEN 6
WHEN 'L' THEN 7
WHEN 'C' THEN 8
END
) = 1
Enthusiast

Re: How to use CASE statement when having multiple hierarchy picks

thank you Dieter!

AM