merge databricks SQL of different decimal datatypes

Question

I have a Databricks SQL search that results in a data type decimal(18,0)

I want to append the results of this search into an existing table (df.write.format("delta").mode("append").save("a_path")) but cannot because it has a data type of decimal(38,18)

When I try to append, the error I get is: AnalysisException: Failed to merge fields 'id' and 'id'. Failed to merge decimal types with incompatible precision 38 and 18 & scale 18 and 0;

Is there a way around this?

I tried to cast the result of the search to a decimal(38,18) select cast(id decimal(38,18))... but this did not work

Any suggestions

Answer 1

As a work around, I converted the SQL search columns into decimal type in pyspark, and then continued to merge:

query="""select * from ..."""

df=spark.sql(query)
df=df.withColumn("id",df["id"].cast(DecimalType(38,18)))
df.write.format("delta").mode("append").save("a_path")