how to write case statement with update in hive

Question

UPDATE  fact_orders1
SET m_order_created = (CASE WHEN fact_orders1.order_number = so.order_num THEN 1 ELSE 0 END)
WHERE order_number in (
  SELECT so.order_num 
  FROM s_order so
  JOIN s_order_item soi ON so.ROW_ID = soi.ORDER_ID
  JOIN s_product sp ON soi.PROD_ID = sp.ROW_ID
  JOIN s_order_type sot ON so.ORDER_TYPE_ID = sot.ROW_ID 
  JOIN s_order_x sox ON so.ROW_ID=sox.PAR_ROW_ID
  WHERE (SUBSTR(LTRIM(sp.NAME), 1,3) IN ('VIP','BIZ')
  OR sp.NAME IN ('HSBB Business 10 Mbps','HSBB Business 20 Mbps','HSBB Residential 10 Mbps','HSBB Residential 20 Mbps'))
  AND sot.NAME = 'New Install' AND sp.PROD_CD = 'Promotion' AND sox.ATTRIB_11 = 'Y'
)

Error

====
FAILED: SemanticException [Error 10004]: Line 1:82 Invalid table alias or column reference 'so': (possible column names are: order_number, created, last_upd, m_order_created)

How to solve this?

54l3d 54l3d · Accepted Answer · 2015-12-31T15:23:52

You cannot reference table from the subquery in the outer query, the error is in the 2nd line :

CASE WHEN fact_orders1.order_number = so.order_num THEN 1 ELSE 0 END

so qualifier is will not be evaluated, you need to join fact_orders1 and order_num in order to match order_number and order_num.

how to write case statement with update in hive

1 Answers