SQL Subquery using where condition

SELECT
    RE.RECODE,
    (SELECT
        X.HIDESC
    FROM
            (
            SELECT HI.HIDESC, ROW_NUMBER() OVER(PARTITION BY HI.HIRECO ORDER BY HI.HIRECO) AS RN
            FROM M6HI HI WHERE HI.HIRECO = RE.RECODE
            ) X
    WHERE X.RN = 1) AS NILAI
FROM M5RE RE

Sorry for my bad english.

I want to get HIDESC from M6HI based on HI.HIRECO = RE.RECODE and RE.RECODE is loop from M5RE.

Anyone know how to fix it?

Answer

A Common Table Expression may help you break this down a bit.

WITH cte_hidesc AS (
 SELECT HI.HIDESC, ROW_NUMBER() OVER(PARTITION BY HI.HIRECO ORDER BY HI.HIRECO) AS RN
 FROM M6HI HI INNER JOIN M5RE RE ON HI.HIRECO = RE.RECODE
)

SELECT HIDESC
FROM cte_hidesc
WHERE ROWNUMBER = 1

HiveQL supports CTE’s. Documentation is here:
https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression