Taking Second Value with Case When in Hana Tables Join Condition

Question

I have event, cases and roads tables.

I want to join these 3 tables but i have a problem because there is two different road_id which matched same firstpoint, lastpoint, nation and factory columns.

I want the when case is special then use the 2nd matching road id, but the data is always duplicate with two different Road_ID.

i tried the following example but it couldnt work. How can i add this condition without duplicate?

Thanks a lot.

 select Nation, Factory, FirstPoint, LastPoint, Road_ID, Count(E.Eventnumber)
 FROM  "EVENTS" as   "E"
 inner join "CASES" "C" on "E"."CaseId" = "C"."CaseId"
 inner join "ROADS" "R" ON "C"."FirstPoint"="R."FirstPoint" and "C"."LastPoint"="R."LastPoint" 
 and "C"."Nation" = "R"."Nation" and "C"."Factory" = "R."Factory"
 and CASE WHEN "C"."Type"='Special' THEN "R".FIRST(Road_ID) ELSE "R".SECOND (Road_ID)END 
 where "C"."Status"='Completed' 
 Group by 
 Road_ID,
 Factory,
 Nation,
 FirstPoint,
 LastPoint

Road Table

Road_ID	Nation	Factory	FirstPoint	Last Point
A9FAKFAAA	Finland	Sweden	A Point	B Point
FKAFAOWWW	Finland	Sweden	A Point	B Point
NEQL003FA	Finland	Sweden	C Point	D Point
LWW922FAF	Finland	Sweden	C Point	D Point
UJJA83AF	Norway	Estonia	A Point	B Point
9FAWAK33	Norway	Estonia	A Point	B Point

Output Table

Road_ID	Nation	Factory	FirstPoint	Last Point	Type
A9FAKFAAA	Finland	Sweden	A Point	B Point	Special
FKAFAOWWW	Finland	Sweden	A Point	B Point	Normal
LWW922FAF	Finland	Sweden	C Point	D Point	Normal
9FAWAK33	Norway	Estonia	A Point	B Point	Special

Answer 1

Re-reading this question I am still not sure to correctly understand the requirement.

The way I read this is this:

• tables CASES and ROADS can be joined by the compound key Nation ,Factory,FirstPoint,LastPoint
• in table ROADS there are multiple entries for any combination of Nation ,Factory,FirstPoint,LastPoint
• when joined with CASES with "TYPE"= 'SPECIAL' one of the multiple entries in ROADS should be matched, and a different one when "TYPE"='NORMAL'

I would approach this with two outer joins, one for each "case type".

Something like this should do the trick:

WITH rankedRoads as (
    SELECT
        "Road_ID", "FirstPoint", "LastPoint", "Nation", "Factory"
        , RANK() OVER (PARTITION BY "FirstPoint", "LastPoint", "Nation", "Factory"
                       ORDER BY "Road_ID" ) as ROAD_RANK
    FROM ROADS 
)
SELECT
    "C"."FirstPoint", "C"."LastPoint", "C"."Nation", "C"."Factory"
    , COALESCE ("R_N"."Road_ID", "R_S"."Road_ID") as "Road_ID"
    , COUNT(E.Eventnumber)
FROM  
    "EVENTS" as   "E"
    inner join "CASES" "C" 
        on "E"."CaseId" = "C"."CaseId"
    
    left outer join "rankedRoads" "R_N" 
        ON ("C"."FirstPoint", "C"."LastPoint", "C"."Nation", "C"."Factory")
         = ("R_N"."FirstPoint", "R_N"."LastPoint", "R_N"."Nation", "R_N"."Factory")
         AND "C"."Type" = 'Normal'
         AND "R_N".ROAD_RANK = 1

    left outer join "rankedRoads" "R_S" 
        ON ("C"."FirstPoint", "C"."LastPoint", "C"."Nation", "C"."Factory")
         = ("R_S"."FirstPoint", "R_S"."LastPoint", "R_S"."Nation", "R_S"."Factory")
         AND "C"."Type" = 'Special'
         AND "R_S".ROAD_RANK = 2
WHERE
    "C"."Status" = 'Completed' 
GROUP BY  
    "C"."FirstPoint", "C"."LastPoint", "C"."Nation", "C"."Factory"
    , COALESCE ("R_N"."Road_ID", "R_S"."Road_ID") as "Road_ID";