[FIXED] How do use multiple subquery within left joins to retrieve connecting data points

Issue

Hi having some issues with my code not sure what is meant by missing keywords.

SELECT  a.equipment_id, a.equipment_description, a.failure_class,
        b.equipment_display_mapping_id,
        c.equipment_display_category_name,
        d.link_id, d.location_id,
        e.rd_attr_txt, e.node_fr_id_num, e.node_to_id_num,
        f.node_to_id_num as Previous_node
FROM Equipment_Detail a
LEFT JOIN equipment_display_mapping b on b.equipment_id = a.equipment_id
LEFT JOIN equipment_display_category c on c.equipment_display_category_id =  b.equipment_display_category_id
LEFT JOIN equipment_location_mapping d on d.equipment_id = a.equipment_id
LEFT JOIN vw_gis_link e on e.lk_id_num = d.link_id
LEFT JOIN 
    (
        SELECT node_to_id_num 
        FROM vw_bis_link_details
        WHERE node_to_id_num = e.node_fr_id_num
    ) as f on d.link_id = f.link_id
WHERE 1=1 and a.equipment_type_id = 113;

getting the following error:

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:

*Action:
Error at Line: 17 Column: 7

Solution

Here you join using link_id but you only select the node_to_id_num. Also in Oracle if I remeber correctly you cannot use AS when aliasing tables. So substitue as f with f

(
    SELECT node_to_id_num 
    FROM vw_bis_link_details
    WHERE node_to_id_num = e.node_fr_id_num
) f on d.link_id = f.link_id

Answered By - Gabriel Durac

Answer Checked By - Marie Seifert (PHPFixing Admin)