cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Adalberto
New Contributor II

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 

)

1 ACCEPTED SOLUTION

Accepted Solutions

Noopur_Nigam
Valued Contributor II
Valued Contributor II

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.

View solution in original post

4 REPLIES 4

Hubert-Dudek
Esteemed Contributor III

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

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

@Hubert Dudek​ , Yes. Same error 😞

Noopur_Nigam
Valued Contributor II
Valued Contributor II

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.

Hi @Adalberto Garcia Espinosa​, 

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

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.