Casting a String Column to 2 Digit scale in Hive Query Language.

Anurag

Casting a String Column to 2 Digit scale in Hive Query Language.

I have a select statement as given below………..

SELECT tbl1.Name AS Material_name
, CAST (tbl1.sale_rev AS DECIMAL(17,2)) AS revenue
, CASE WHEN tbl1.PrevRevenue <> 0 THEN
CAST(tbl1.PresRevenue/tbl1.PrevRevenue*100 AS STRING)
ELSE 'NA'
END AS Rtrend

I want to cast the Rtrend as a decimal with maximum 2 point scale (17.12) I have to cast it as String because WHEN tbl1.PrevRevenue <> 0 is not met I have to show it as 'NA'
I tried doing it as follows

CASE WHEN tbl1.PrevRevenue <> 0 THEN CAST((CAST(tbl1.PresRevenue/tbl1.PrevRevenue*10) AS DECIMAL(17,2)) AS STRING)
ELSE 'NA'
END AS Rtrend

but I am getting a syntax error. The revenue part is projected as expected. I want the revenue trend part also to be projected like revenue. Is there any way to achieve this?