select statement inside case statement for impala

Question

I am in need of eliminating the hardcoded values in the case and have developed a lookup table to be used to get the values.

The lookup table looks like:

serial_no   code    description type

1       J   Jol           CENTRE_TYPE
3       C   Cols          CENTRE_TYPE
4       R   Rud           CENTRE_TYPE
2       A   TCD           CENTRE_TYPE

I have the below impala code:

SELECT `prtc`,
    CASE   WHEN LEFT(prtc,1) = 'C' THEN  (SELECT DESCRIPTION FROM dev.lookup_table WHERE CODE='C' AND TYPE='CENTRE_TYPE')
            WHEN LEFT(prtc,1) = 'A' THEN (SELECT DESCRIPTION FROM dev.lookup_table WHERE CODE='A' AND TYPE='CENTRE_TYPE')
            WHEN LEFT(prtc,1) = 'R' THEN (SELECT DESCRIPTION FROM dev.lookup_table WHERE CODE='R' AND TYPE='CENTRE_TYPE')
            WHEN LEFT(prtc,1) = 'J' THEN (SELECT DESCRIPTION FROM dev.lookup_table WHERE CODE='J' AND TYPE='CENTRE_TYPE')
    END AS CENTRE_TYPE
FROM dev.`tablename`

I am getting an error as "AnalysisException: Could not resolve table reference: 'dev.lookup_table'"

Answer 1

Use join:

SELECT t.prtc,
       l.DESCRIPTION  as CENTRE_TYPE
FROM dev.`tablename` t
     left outer join dev.lookup_table l on LEFT(t.prtc,1)= l.CODE and l.TYPE='CENTRE_TYPE'