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)
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!!
