Convert Array to VARCHAR

#1

I have an array in a View.

  1. I need to convert it to a VARCHAR so I can use COALESCE function OR can I use Coalesce against an array element directly?

  2. Would still like to convert array element to VARCHAR so I could use CASE statement.

Tried
select CAST (USE_OF_PROCEEDS [0] as VARCHAR)
FROM
“External_Data.stage0.3529”.fixedIncomeExtNamrV2_history
where USE_OF_PROCEEDS is not null

Response:
SQL Error: VALIDATION ERROR: Cast function cannot convert value of type RecordType(VARCHAR(65536) useOfProceeds) to type VARCHAR(65536)

SQL Query select CAST (USE_OF_PROCEEDS [0] as VARCHAR)

FROM

“External_Data.stage0.3529”.fixedIncomeExtNamrV2_history

where USE_OF_PROCEEDS is not null

#2

What is the contents of the USE_OF_PROCEEDS column? Maybe you need to specify the item from the struct… such as USE_OF_PROCEEDS[0][‘useOfProceeds’]

You can also use the TYPEOF SQL function to confirm the datatype returned in a projection column.

#3

@anthonyp Do this

If the column use_of_proceeds shows a list icon like the below screenshot, you need to first unnest using UI or in SQL use flatten

flatten(balances) AS balances

image

If it is a struct (see icon), then you can directly extract the element by clicking the three dots inside the field (…) or use SQL function like below

balances.balanceusdeamount