We are migrating Greenplum to HiveSQL and please help me as below statement needs to convert. kindly help us.

GREENPLUM - ||trim(to_char(sum(revenue),'9,999,999,999'))

hive will not supported to_char so that equal property we have to put in hive.

CodePudding user response：

You can use cast(col as STRING) to convert to char and use format_number(146452664,0) to display as comma separated value in hive. here you sql should look like -

SUBSTR(cast( format_number(sum(revenue)),0) as string),1,13)

I am using a substring in the end to get only 10 integers.