title: SQL alternative to sub-query in SELECT Item list
body: <p>I have RDBMS table and Queries which are working perfectly. I have offloaded data from RDBMS to HIVE table.To run the existing queries on HIVE, we need first to make them compatible to HIVE.  </p>
  
  <p>Let's take below example with sub-query in select item list. It is syntactically valid and working fine on RDBMS system.  But It Will not work on HIVE As per <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries" rel="nofollow">HIVE manual</a> , Hive supports subqueries only in the FROM and WHERE clause.   </p>
  
  <p><strong>Example 1 :</strong></p>
  
  <pre><code>SELECT t1.one
      ,(SELECT t2.two 
          FROM TEST2 t2 
          WHERE t1.one=t2.two) t21
      ,(SELECT t3.three 
          FROM TEST3 t3 
          WHERE t1.one=t3.three) t31
  FROM TEST1 t1 ;
  </code></pre>
  
  <p><strong>Example 2:</strong> </p>
  
  <pre><code>SELECT a.*
     , CASE
            WHEN EXISTS
                   (SELECT 1
                      FROM tblOrder O
                        INNER JOIN tblProduct P
                                ON O.Product_id = P.Product_id
                     WHERE O.customer_id        = C.customer_id
                       AND P.Product_Type IN (2, 5, 6, 9)
                   )
            THEN 1
            ELSE 0
     END AS My_Custom_Indicator
     FROM tblCustomer C
     INNER JOIN tblOtherStuff S
         ON C.CustomerID = S.CustomerID ;
  </code></pre>
  
  <p><strong>Example 3 :</strong></p>
  
  <pre><code>Select component_location_id, component_type_code,
    ( select clv.LOCATION_VALUE 
    from stg_dev.component_location_values clv 
    where  identifier_code = 'AXLE' 
         and component_location_id = cl.component_location_id ) as AXLE,
    ( select clv.LOCATION_VALUE 
    from stg_dev.component_location_values clv 
    where identifier_code = 'SIDE' 
         and component_location_id = cl.component_location_id ) as SIDE
    from stg_dev.component_locations cl ;
  </code></pre>
  
  <p>I want to know the possible alternative of sub-queries in select item list to make it compatible to hive. Apparently I will be able to transform existing queries in HIVE format.</p>
  
  <p>Any help and guidance is highly appreciated !     </p>