04-13-2022 08:30 AM
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
)
05-13-2022 08:52 AM
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.
04-13-2022 12:01 PM
@Adalberto Garcia Espinosa , Have you tried doubles only?
Fctor_HL_Estiba double GENERATED ALWAYS AS (cast(10000 as double) div Khe),
04-13-2022 04:48 PM
@Hubert Dudek , Yes. Same error 😞
05-13-2022 08:52 AM
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.
06-07-2022 11:31 AM
Hi @Adalberto Garcia Espinosa,
Just a friendly follow-up. Did Noopur's response help you to resolve your issue? Please let us know.
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group