topic Re: SQL calculated field not recognized in load script in QlikView

SQL calculated field not recognized in load script

leenlart — Mon, 01 Oct 2018 15:41:01 GMT

Hello,

I am trying to add a calculated field into my load script. The field is based on multiple if's, and it sets the percentage that will later be applied to the data.

My sql code works fine when I use Toad to query directly on the db. But when I paste it into my QV load, I get an error.

My SQL load statement is the following :

Situations:

LOAD

CSOC as CSOC_SIT,

CETAB as CETAB_SIT,

PRECHRO AS PRECHRO_SIT,

CDEPOT AS CDEPOT_SIT,

text(CART) as CART_SIT,

LOT,

CEMPL AS CEMPL_SIT,

DATESIT,

QTEBASE1 AS QTEBASE1_SIT,

CUNITEBASE1 AS CUNITEBASE1_SIT,

QTEBASE2 AS QTEBASE2_SIT,

CUNITEBASE2 AS CUNITEBASE2_SIT,

QTEBASE3 AS QTEBASE3_SIT,

CUNITEBASE3 AS CUNITEBASE3_SIT,

ID as %ID_SIT,

CSOCJ AS CSOCJ_SIT ,

CSTATUT AS CSTATUT_SIT,

LSTATUT AS LSTATUT_SIT,

CETAT AS CETAT_SIT,

TxProvision

;

SQL select t0.CSOC, t0.CETAB, t0.PRECHRO, t0.CDEPOT,t0.CART, t0.LOT,t0.CEMPL,t0.DATESIT,t0.QTEBASE1,t0.CUNITEBASE1,t0.QTEBASE2, t0.CUNITEBASE2, t0.QTEBASE3,t0.CUNITEBASE3,

t0.ID,t0.CSOCJ, t1.CSTATUT as CSTATUT, t1.LSTATUT as LSTATUT, T1.CETAT as CETAT,

(case when t1.CSTATUT in ('B04','B08','B09','B10','B11','B12','B13','B14','B15','BAR','L','X','FQR') then 1

else

(case when T3.CCRI = '$DLC' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 1

else

(case when T3.CCRI = '$DLUO' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 1

else

(case when t1.CSTATUT in ('AC','ACQ','AT','B','B01','B02','B03','B05','B06','CE','DES','K','N','O','R','U','Y','H01','HO2','HOL') then 0.25

else

(case when t1.CSTATUT in ('W','V') then 0.5

else

(case when T3.CCRI = '$DLS' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 0.25

else 0

end )

end)

end )

end)

end) as TxProvision

from dsitstk t0

left outer join dlote t1 on

t0.lot=t1.lot and t0.cetab=t1.cetab and t0.csoc=t1.csoc

left outer join dlotc t3 on

t0.lot=t3.lot and t0.cetab=t3.cetab and t0.csoc=t3.csoc

where

t1.dateeffet= (select max(t2.dateeffet) from dlote t2 where t2.lot=t1.lot and trunc(t2.dateeffet)<=trunc(t0.datesit))

and trunc(t0.datesit) >= to_date('$(vDateMin)','DD-MM-YYYY')

union

select t0.CSOC, t0.CETAB, t0.PRECHRO, t0.CDEPOT,t0.CART, t0.LOT,t0.CEMPL,t0.DATESIT,t0.QTEBASE1,t0.CUNITEBASE1,t0.QTEBASE2, t0.CUNITEBASE2, t0.QTEBASE3,t0.CUNITEBASE3,

t0.ID, t0.CSOCJ, ' ' as CSTATUT, ' ' as LSTATUT, ' ' as CETAT, 0 as TxProvision

from dsitstk t0

where not exists (select 'X' from dlote t2 where t2.lot=t0.lot and trunc(t2.dateeffet)<=trunc(t0.datesit))

and trunc(t0.datesit) >= to_date('$(vDateMin)','DD-MM-YYYY') ;

My error is "Field not found - <TxProvision>".

I've tested both parts of my union separately in SQL

Can anyone see what I'm not seeing here?

Thanks!!

Re: SQL calculated field not recognized in load script

marcus_sommer — Mon, 01 Oct 2018 15:50:08 GMT

Just as test - comment out the preceeding load and if it loads without an error you could switch it to a resident load.

- Marcus

Re: SQL calculated field not recognized in load script

leenlart — Mon, 01 Oct 2018 15:56:59 GMT

I'm not sure I follow you.

What preceeding load ?

I have this as basically the first section in my load script.

Re: SQL calculated field not recognized in load script

olivierrobin — Mon, 01 Oct 2018 16:01:20 GMT

hello

try to to write the name in capital letters

TXPROVISION in the load statement

Re: SQL calculated field not recognized in load script

vishsaggi — Mon, 01 Oct 2018 16:05:16 GMT

Marcus mean to try your SQL Select as is and check if it runs fine. Like below

Situations:

SQL select t0.CSOC, t0.CETAB, t0.PRECHRO, t0.CDEPOT,t0.CART, t0.LOT,t0.CEMPL,t0.DATESIT,t0.QTEBASE1,t0.CUNITEBASE1,t0.QTEBASE2, t0.CUNITEBASE2, t0.QTEBASE3,t0.CUNITEBASE3,

t0.ID,t0.CSOCJ, t1.CSTATUT as CSTATUT, t1.LSTATUT as LSTATUT, T1.CETAT as CETAT,

(case when t1.CSTATUT in ('B04','B08','B09','B10','B11','B12','B13','B14','B15','BAR','L','X','FQR') then 1

else

(case when T3.CCRI = '$DLC' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 1

else

(case when T3.CCRI = '$DLUO' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 1

else

(case when t1.CSTATUT in ('AC','ACQ','AT','B','B01','B02','B03','B05','B06','CE','DES','K','N','O','R','U','Y','H01','HO2','HOL') then 0.25

else

(case when t1.CSTATUT in ('W','V') then 0.5

else

(case when T3.CCRI = '$DLS' and trunc(t3.VALD)>=trunc(T0.DATESIT) then 0.25

else 0

end )

end)

end )

end)

end) as TxProvision

from dsitstk t0

left outer join dlote t1 on

t0.lot=t1.lot and t0.cetab=t1.cetab and t0.csoc=t1.csoc

left outer join dlotc t3 on

t0.lot=t3.lot and t0.cetab=t3.cetab and t0.csoc=t3.csoc

where

t1.dateeffet= (select max(t2.dateeffet) from dlote t2 where t2.lot=t1.lot and trunc(t2.dateeffet)<=trunc(t0.datesit))

and trunc(t0.datesit) >= to_date('$(vDateMin)','DD-MM-YYYY')

union

select t0.CSOC, t0.CETAB, t0.PRECHRO, t0.CDEPOT,t0.CART, t0.LOT,t0.CEMPL,t0.DATESIT,t0.QTEBASE1,t0.CUNITEBASE1,t0.QTEBASE2, t0.CUNITEBASE2, t0.QTEBASE3,t0.CUNITEBASE3,

t0.ID, t0.CSOCJ, ' ' as CSTATUT, ' ' as LSTATUT, ' ' as CETAT, 0 as TxProvision

from dsitstk t0

where not exists (select 'X' from dlote t2 where t2.lot=t0.lot and trunc(t2.dateeffet)<=trunc(t0.datesit))

and trunc(t0.datesit) >= to_date('$(vDateMin)','DD-MM-YYYY') ;

If this Runs fine on your SQL end check once?

Re: SQL calculated field not recognized in load script

marcus_sommer — Tue, 02 Oct 2018 05:37:06 GMT

The following was meant (simplified) to switch from:

table:

load *;

sql select * from source;

to:

tableTemp:

sql select * from source;

table:

load * resident tableTemp;

- Marcus

Re: SQL calculated field not recognized in load script

leenlart — Fri, 09 Nov 2018 07:55:36 GMT

Thanks for your help guys.

I ended up switching my sql around (it wasn't 100% correct) and then loading into a temporary table.

Then I do my case on the temporary table.