I have an array in a View.
-
I need to convert it to a VARCHAR so I can use COALESCE function OR can I use Coalesce against an array element directly?
-
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
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.
@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
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
Apologies for the delay in responding, just back in the office.
Dremio WEB GUI displays a value of “WyB7CiAgInVzZU9mUHJvY2VlZHMiIDogIkdlbmVyYWwgQ29ycG9yYXRlIFB1cnBvc2VzIgp9IF0=”
DBeaver (a SQL Client) displays the value as [ { “useOfProceeds” : “General Corporate Purposes” }, { “useOfProceeds” : “Refinance” } ]
TYPEOF (USE_OF_PROCEEDS) gives me VARBINARY. Based on the values, it appears to be a List or Array in binary form.
Any suggestions on how to convert this so I get a VARCHAR or at least usable Array type?
@anthonyp Got it now, what source is this?
Its from a .parquet file.
@anthonyp
I want to try some commands, are you able to send a portion of the Parquet file (like sample data)?