cannot resolve '(CAST(10000 AS BIGINT) div Khe)' due to data type mismatch:

[Adalberto]

Hi,

I'm trying to create a delta table using SQL but I'm getting this error

Error in SQL statement: AnalysisException: cannot resolve '(CAST(10000 AS BIGINT) div Khe)' due to data type mismatch: differing types in '(CAST(10000 AS BIGINT) div Khe)' (bigint and double).; line 1 pos 0;

I've tried different combinations but still the same error. Anyone knows how to fix it. Here is the whole create statement.

Thanks

CREATE OR REPLACE TABLE Productos(

Cdgo_Prdcto varchar(10) NOT NULL,

Dscrpcion varchar(100) NOT NULL,

Cdgo_Unico varchar(10) NOT NULL,

Vlmen float,

Volumen_Undad varchar(10),

Cpcdad varchar(10),

Khe double NOT NULL,

Cdgo_Ctgria varchar(5) NOT NULL,

Cdgo_Mrca varchar(5) NOT NULL,

Cdgo_Tpo_Envse varchar(5) NOT NULL,

Cdgo_Msto int,

Cdgo_Orgen_Rem varchar(10),

Cdgo_Cja varchar(10),

Cdgo_Estba varchar(10),

Cdgo_Envse varchar(10),

Grpo_Ssttcion int,

EsPdreSsttcion boolean,

Cdgo_Envsdo varchar(10),

Cdgo_Trspaso varchar(10),

Es_Rmpque boolean NOT NULL,

Es_Exprtcion boolean NOT NULL,

Unddes_Cja int NOT NULL,

Fctor_Estbas int NOT NULL,

Unidad_Venta int NOT NULL,

Unidad_Medida_Venta varchar(10) NOT NULL,

Csto_Estndar float,

Vgncia boolean NOT NULL,

Vgncia_Plncion boolean NOT NULL,

Vgncia_Plncion_Dria boolean NOT NULL,

Vgncia_Plncion_MPS boolean NOT NULL,

EnCrcion boolean,

COD_PRO varchar(10),

COD_TEN varchar(10),

COD_TEN_REM varchar(10),

Peso_g float,

Ocpcion float,

Fctor_HL_Estiba double GENERATED ALWAYS AS (cast(10000 as bigint) div Khe),

Fctor_HL_UND float GENERATED ALWAYS AS (10000 * khe) ,

Fctor_HL_Cajas bigint GENERATED ALWAYS AS (cast(10000 as float) div (cast(khe as float) div unddes_cja)),

Temp_Vngcia_TMS boolean ,

Vol_Id int ,

Prsntcion_Id int ,

Prcio_Vnta float ,

Cdgo_Dmnda varchar(10) ,

Es_Promo boolean ,

Mrgen_Gncia float ,

Csto_Estndar_Pryctdo float ,

Excise_HL float ,

Jrrquia varchar(20) ,

Es_Cmbo boolean ,

Es_NPD boolean ,

FechaInicio_NPD timestamp ,

FechaFin_NPD timestamp ,

Cdgo_Capacidad varchar(10) ,

Usrio_Retro_Invntrios varchar(50) ,

Arrume float ,

Cjas_por_Nvel int ,

Nvel_Prmtdo int ,

Arrume_Estanteria float ,

APILADO varchar(10) ,

PesoNeto_g float 

)

[Hubert-Dudek]

@Adalberto Garcia Espinosa​ , Have you tried doubles only?

Fctor_HL_Estiba double GENERATED ALWAYS AS (cast(10000 as double) div Khe),

[Adalberto]

@Hubert Dudek​ , Yes. Same error 😞

[Noopur_Nigam · Accepted solution]

Hi @Adalberto Garcia Espinosa​ Do you need khe column to be double? If not, below query is working:

%sql CREATE OR REPLACE TABLE Productos(Khe bigint NOT NULL,Fctor_HL_Estiba bigint GENERATED ALWAYS AS (cast(10000 as bigint) div Khe)) seems to be working.

[jose_gonzalez]

Hi @Adalberto Garcia Espinosa​, 

Just a friendly follow-up. Did Noopur's response help you to resolve your issue? Please let us know.