- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
)
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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),
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-13-2022 04:48 PM
@Hubert Dudek , Yes. Same error 😞
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.