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.
HIERARCHY given by business:
I would greatly appreciate for a quick solution.
Put a filter using ROW_NUMBER in a Derived Table and join to it:
OVER (PARTITION BY LINE_ITEM
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
) = 1