topic Re: Trouble while copying SQL into QlikView in QlikView

Trouble while copying SQL into QlikView

cristian_av — Wed, 25 Nov 2020 16:16:04 GMT

I've copied an SQL code that I've previously tried in SQL Server manager and that was working there.

In QlikView I get "ErrorSource: (null), ErrorMsg: (null)"

What could be the mistake? I'm using multiple Inserto into because I use temporary tables in SQL Server.

Thanks!!

LOAD *;

SQL INSERT INTO #Clasif1

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'FOB Fruta Exportacion'),

kilos = Convert(decimal(14,2),SUM(de.dece_kilrea)),

total_plata = Convert(decimal(14,2),sum((de.dece_kilrea/de.enva_pesone)*de.dece_fobuni))

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

and de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

INSERT INTO #Clasif1

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Servicio de Exportación'),

kilos = SUM(de.dece_kilrea), total_plata = sum(de.dece_valcom)*-1

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

INSERT INTO #Clasif1

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Materiales & Otros Servicios'),

kilos = SUM(de.dece_kilrea), total_plata = sum(de.dece_totcos - de.dece_valcom)*-1

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

INSERT INTO #Clasif1

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Bonificación Servicio'),

kilos = SUM(de.dece_kilrea), total_plata = sum(isnull(de.dece_bonser,0))

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

AND isnull(de.dece_bonser,0) <> 0

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

Having sum(isnull(de.dece_bonser,0)) <> 0

SELECT cla.pate_tempor, cla.prod_codigo, cla.concepto, cla.kilos, cla.total_plata, cla.liqu_numero

FROM #Clasif1 cla;

Re: Trouble while copying SQL into QlikView

cristian_av — Thu, 06 Oct 2016 11:44:36 GMT

You cannot perform an INSERT command from QV. You can create a stored procedure that runs your code above and call that.

SQL exec stored_procedure <parameters>

mickeger

Re: Trouble while copying SQL into QlikView

tunoi — Thu, 06 Oct 2016 12:10:36 GMT

Why dont you use:

LOAD *;

SQL

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'FOB Fruta Exportacion'),

kilos = Convert(decimal(14,2),SUM(de.dece_kilrea)),

total_plata = Convert(decimal(14,2),sum((de.dece_kilrea/de.enva_pesone)*de.dece_fobuni))

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

and de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

union all

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Servicio de Exportación'),

kilos = SUM(de.dece_kilrea), total_plata = sum(de.dece_valcom)*-1

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

union all

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Materiales & Otros Servicios'),

kilos = SUM(de.dece_kilrea), total_plata = sum(de.dece_totcos - de.dece_valcom)*-1

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

union all

SELECT de.pate_tempor, de.prod_codigo, de.liqu_numero,

concepto = Convert(char(50),'Bonificación Servicio'),

kilos = SUM(de.dece_kilrea), total_plata = sum(isnull(de.dece_bonser,0))

FROM dba.detacajemb de

WHERE de.pate_tempor = 2015

AND de.pool_tipool = 1

AND isnull(de.dece_bonser,0) <> 0

GROUP BY de.pate_tempor, de.prod_codigo, de.liqu_numero

Having sum(isnull(de.dece_bonser,0)) <> 0

Re: Trouble while copying SQL into QlikView

cristian_av — Fri, 07 Oct 2016 13:58:06 GMT

Thanks! That works too!!