incremental load from non date field

[raZor]

incremental load from non date field

Hi All,

i have a sql script in that i have a filed name  t.doc_no as invoiceno, //*****this field's value is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//

this data i am saving in .qvd file. now i want to have another .qvd file that only contains incremental data from sql database

attached is my script.

can any one suggest me how my script should be using variable?

Thanks in advance

// *****START CODE Variable Declaration*****//
Let vtoday = date(today());
// PAth for connexion
Let DB_connection = 'pcs';
//Let DB_connection2 = 'PCSOFT';

// ***** Repertory for the QVD files*****//
LET name_app = '$Sales_Data';
//name of the table
LET table_1 = 'A';
//LET table_2 = 'NonTest';
// ***** PATH of QVD files *****//
LET vSaveQVD = 'lib://test/';
LET qvd_1 = '$(vSaveQVD)/$(name_app)/$(table_1).qvd';

// **************************** Variable Declaration - END CODE *****************************//

//****SQL DATA****//

$(name_app):
LIB CONNECT TO '$(DB_connection)';
SQL select

case when left(t.dc,1) = 'U' then 'Pantr'
when left(t.dc,1) = 'V' then 'Vir'
when left(t.dc,1) = 'G' then 'Gok'
when left(t.dc,1) = 'H' then 'HO'
when left(t.dc,1) = 'B' then 'Bhi'
when left(t.dc,1) = 'M' then 'Mu'
when left(t.dc,1) = 'O' then 'occ' else ' ' end as location,max(st.target) as saletarget,max(st.volume) as volume,
case when left(t.dc,1) = 'U' then 909.2
when left(t.dc,1) = 'V' then 131.1
when left(t.dc,1) = 'G' then 787.7
else 0 end as sale_target,
c.type_desc as territory,

t.doc_no as invoiceno //*****this field is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//,

t.doc_date as doc_date, t.party_no,
cast(pm.PARTY_NAME as varchar(60)) as party_name,
pm.cics,
CASE WHEN PM.PARTY_CAT = '1' THEN 'FOOD' WHEN PM.PARTY_CAT = '2' THEN 'PHARMA' WHEN PM.PARTY_CAT = '3' THEN 'FEED'
WHEN PM.PARTY_CAT = '4' THEN 'INDUSTRY' ELSE pm.OUR_CODE END AS segment,
max(i.IM_CODE) as im_code,
cast(max(m.im_descr) as varchar(50)) as im_descr,
sum(i.im_qty) qty,
i.im_salert as Rate,
sum(i.IM_BASIC) as amount,
sum(i.IM_BASIC)/10000000 as Total_IN_CR,
sum(i.doc_tax0) as Dis,
sum(i.doc_tax1) as Frt,
sum(i.doc_tax3) as Excise,
sum(i.doc_tax20) as IGST,
sum(i.doc_tax21) as CGST,
sum(i.doc_tax22) as SGST,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22) as Total,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22)/10000000 as TotalINCR,
max(ordterms.FRT_DSC) as frtdescr,
MAX(T.DOC_TAX6) AS TCS,
MAX(t.DOC_TAX7) AS FRIEGHT,
max(pm.party_grp) as partygroup,
max(tp.party_no) as Trans_Code,
cast(max(tp.PARTY_NAME) as varchar(50)) as transporter,
TA.TO_DEST as to_dest,
max(T.rdoc_no) dino,
max(t49.doc_date) as didate,
PM.state_code,
sst.STATE_NAME,
PM.GSTREGNO,
m.IM_MATCL,
cl.MATCL_DESC as RGC
from trinvs t
left join imtrans i on t.doc_type = i.doc_type and t.doc_no = i.doc_no
left join partymst pm on t.party_st = pm.party_st and t.party_no = pm.PARTY_NO
left join maitemh m on i.im_code = m.im_code
left join conact c on c.ACC_TYPE = pm.ACC_TYPE and c.PARTY_ST = pm.PARTY_ST
left join imcntrl cl on cl.IM_MATCL = m.IM_MATCL
LEFT JOIN ORDTERMS ON T.RDOC_NO = ORDTERMS.DOC_NO AND T.RDOC_TYPE = ORDTERMS.DOC_TYPE
LEFT JOIN PARTYMST TP ON T.TRNP_ST = TP.PARTY_ST AND T.TRNP_NO = TP.PARTY_NO
LEFT JOIN TRINVS T49 ON T.RDOC_NO = T49.DOC_NO AND T.RDOC_TYPE = T49.DOC_TYPE
LEFT JOIN TRINVSA TA ON T.DOC_TYPE = TA.DOC_TYPE AND T.DOC_NO = TA.DOC_NO
LEFT JOIN partyloc l1 on t.party_st = l1.party_st AND t.party_no = l1.party_no AND t.party_bloc = l1.party_bloc
LEFT JOIN state_std SST ON PM.state_code = SST.State_Code
LEFT JOIN STATE_STD SHIPST ON L1.STATE_CODE = SHIPST.State_Code
left join saletarget st on st.plantid = left(t.dc,1) and st.monthid=MONTH(getdate())
where

(LTRIM(T.DOC_TYPE) IN ('46','27') AND LEFT(M.FG_GRP,2) IN ('PR','BP') AND LEFT(T.DOC_NO,5) NOT IN ('GSAL2','GSALO') ) AND I.IM_QTY <> 0 and (MONTH(t.DOC_DATE) = MONTH(getdate()) AND YEAR(t.DOC_DATE) = YEAR(getdate()) )
--and left(t.dc,1) IN ('U','G','V')
and pm.PARTY_NO not in ('R0200','R0156','R0198','R0138','R0207','R0141','R0206','GW033','R0277','R0294','R0296','R0302','R0311','S2056','L0056','T0223','S1987','G0239')
group by left(t.dc,1),
t.party_no,
pm.PARTY_NAME,
pm.CICS,
PM.PARTY_CAT,
pm.OUR_CODE,
i.IM_CODE,
m.im_descr,
c.type_desc,
t.doc_no,
t.doc_date,
i.im_salert,
TA.TO_DEST,
PM.state_code,
sst.STATE_NAME,
PM.GSTREGNO,
m.IM_MATCL,
cl.MATCL_DESC

UNION

select
case when left(t.dc,1) = 'U' then 'Pantr'
when left(t.dc,1) = 'V' then 'Vir'
when left(t.dc,1) = 'G' then 'Gok'
when left(t.dc,1) = 'H' then 'HO'
when left(t.dc,1) = 'B' then 'Bhi'
when left(t.dc,1) = 'M' then 'Mu'
when left(t.dc,1) = 'O' then 'occ' else ' ' end as location,max(st.target) as saletarget,max(st.volume) as volume,
case when left(t.dc,1) = 'U' then 909.2
when left(t.dc,1) = 'V' then 131.1
when left(t.dc,1) = 'G' then 787.7
else 0 end as sale_target,
c.type_desc as territory,

t.doc_no as invoiceno, //*****this field is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//

t.doc_date as doc_date,
t.party_no,
cast(pm.PARTY_NAME as varchar(60)) as party_name,
pm.cics,
CASE WHEN PM.PARTY_CAT = '1' THEN 'FOOD' WHEN PM.PARTY_CAT = '2' THEN 'PHARMA' WHEN PM.PARTY_CAT = '3' THEN 'FEED'
WHEN PM.PARTY_CAT = '4' THEN 'INDUSTRY' ELSE pm.OUR_CODE END AS segment,
max(i.IM_CODE) as im_code,
cast(max(m.im_descr) as varchar(50)) as im_descr,
sum(i.im_qty) qty,
i.im_salert as Rate,
sum(i.IM_BASIC) as amount,
sum(i.IM_BASIC)/10000000 as Total_IN_CR,
sum(i.doc_tax0) as Dis,
sum(i.doc_tax1) as Frt,
sum(i.doc_tax3) as Excise,
sum(i.doc_tax20) as IGST,
sum(i.doc_tax21) as CGST,
sum(i.doc_tax22) as SGST,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22) as Total,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22)/10000000 as TotalINCR,
max(ordterms.FRT_DSC) as frtdescr,
MAX(T.DOC_TAX6) AS TCS,
MAX(t.DOC_TAX7) AS FRIEGHT,
max(pm.party_grp) as partygroup,
max(tp.party_no) as Trans_Code,
cast(max(tp.PARTY_NAME) as varchar(50)) as transporter,
TA.TO_DEST as to_dest,
max(T.rdoc_no) dino, max(t49.doc_date) as didate, PM.state_code,sst.STATE_NAME,PM.GSTREGNO,m.IM_MATCL,cl.MATCL_DESC as RGC
from trinvs t
left join imtrans i on t.doc_type = i.doc_type and t.doc_no = i.doc_no
left join partymst pm on t.party_st = pm.party_st and t.party_no = pm.PARTY_NO
left join maitemh m on i.im_code = m.im_code
left join conact c on c.ACC_TYPE = pm.ACC_TYPE and c.PARTY_ST = pm.PARTY_ST
left join imcntrl cl on cl.IM_MATCL = m.IM_MATCL
LEFT JOIN ORDTERMS ON T.RDOC_NO = ORDTERMS.DOC_NO AND T.RDOC_TYPE = ORDTERMS.DOC_TYPE
LEFT JOIN PARTYMST TP ON T.TRNP_ST = TP.PARTY_ST AND T.TRNP_NO = TP.PARTY_NO
LEFT JOIN TRINVS T49 ON T.RDOC_NO = T49.DOC_NO AND T.RDOC_TYPE = T49.DOC_TYPE
LEFT JOIN TRINVSA TA ON T.DOC_TYPE = TA.DOC_TYPE AND T.DOC_NO = TA.DOC_NO
LEFT JOIN partyloc l1 on t.party_st = l1.party_st AND t.party_no = l1.party_no AND t.party_bloc = l1.party_bloc
LEFT JOIN state_std SST ON PM.state_code = SST.State_Code
LEFT JOIN STATE_STD SHIPST ON L1.STATE_CODE = SHIPST.State_Code
left join saletarget st on st.plantid = left(t.dc,1) and st.monthid=MONTH(getdate())
where //(RIGHT(t.doc_no,4))< $(vMaxNo) AND
(LTRIM(T.DOC_TYPE) IN ('46','27') AND LEFT(M.FG_GRP,2) IN ('PR','BP') AND LEFT(T.DOC_NO,5) NOT IN ('GSAL2','GSALO')) AND I.IM_QTY <> 0 and (MONTH(t.DOC_DATE) < MONTH(getdate()) AND YEAR(t.DOC_DATE) = YEAR(getdate()) ) //t.DOC_DATE between '01-apr-2019' and '31-jan-2020'
--and left(t.dc,1) IN ('U','G','V')
and pm.PARTY_NO not in ('R0200','R0156','R0198','R0138','R0207','R0141','R0206','GW033','R0277','R0294','R0296','R0302','R0311','S2056','L0056','T0223','S1987','G0239')
group by left(t.dc,1),t.party_no,pm.PARTY_NAME,pm.CICS,PM.PARTY_CAT,pm.OUR_CODE,i.IM_CODE,m.im_descr,
c.type_desc,t.doc_no,t.doc_date,i.im_salert,TA.TO_DEST,PM.state_code,sst.STATE_NAME,PM.GSTREGNO,m.IM_MATCL,cl.MATCL_DESC
order by t.doc_date,t.doc_no desc;

//****SQL DATA END****//

//****STORE IN QVD****//

store $(name_app) into '$(qvd_1)'(qvd);

[raZor]

Hi All,

i have a sql script in that i have a filed name  t.doc_no as invoiceno, //*****this field's value is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//

this data i am saving in .qvd file. now i want to have another .qvd file that only contains incremental data from sql database

attached is my script.

can any one suggest me how my script should be using variable?

Thanks in advance

// *****START CODE Variable Declaration*****//
Let vtoday = date(today());
// PAth for connexion
Let DB_connection = 'pcs';
//Let DB_connection2 = 'PCSOFT';

// ***** Repertory for the QVD files*****//
LET name_app = '$Sales_Data';
//name of the table
LET table_1 = 'A';
//LET table_2 = 'NonTest';
// ***** PATH of QVD files *****//
LET vSaveQVD = 'lib://test/';
LET qvd_1 = '$(vSaveQVD)/$(name_app)/$(table_1).qvd';

// **************************** Variable Declaration - END CODE *****************************//

//****SQL DATA****//

$(name_app):
LIB CONNECT TO '$(DB_connection)';
SQL select

case when left(t.dc,1) = 'U' then 'Pantr'
when left(t.dc,1) = 'V' then 'Vir'
when left(t.dc,1) = 'G' then 'Gok'
when left(t.dc,1) = 'H' then 'HO'
when left(t.dc,1) = 'B' then 'Bhi'
when left(t.dc,1) = 'M' then 'Mu'
when left(t.dc,1) = 'O' then 'occ' else ' ' end as location,max(st.target) as saletarget,max(st.volume) as volume,
case when left(t.dc,1) = 'U' then 909.2
when left(t.dc,1) = 'V' then 131.1
when left(t.dc,1) = 'G' then 787.7
else 0 end as sale_target,
c.type_desc as territory,

t.doc_no as invoiceno //*****this field is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//,

t.doc_date as doc_date, t.party_no,
cast(pm.PARTY_NAME as varchar(60)) as party_name,
pm.cics,
CASE WHEN PM.PARTY_CAT = '1' THEN 'FOOD' WHEN PM.PARTY_CAT = '2' THEN 'PHARMA' WHEN PM.PARTY_CAT = '3' THEN 'FEED'
WHEN PM.PARTY_CAT = '4' THEN 'INDUSTRY' ELSE pm.OUR_CODE END AS segment,
max(i.IM_CODE) as im_code,
cast(max(m.im_descr) as varchar(50)) as im_descr,
sum(i.im_qty) qty,
i.im_salert as Rate,
sum(i.IM_BASIC) as amount,
sum(i.IM_BASIC)/10000000 as Total_IN_CR,
sum(i.doc_tax0) as Dis,
sum(i.doc_tax1) as Frt,
sum(i.doc_tax3) as Excise,
sum(i.doc_tax20) as IGST,
sum(i.doc_tax21) as CGST,
sum(i.doc_tax22) as SGST,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22) as Total,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22)/10000000 as TotalINCR,
max(ordterms.FRT_DSC) as frtdescr,
MAX(T.DOC_TAX6) AS TCS,
MAX(t.DOC_TAX7) AS FRIEGHT,
max(pm.party_grp) as partygroup,
max(tp.party_no) as Trans_Code,
cast(max(tp.PARTY_NAME) as varchar(50)) as transporter,
TA.TO_DEST as to_dest,
max(T.rdoc_no) dino,
max(t49.doc_date) as didate,
PM.state_code,
sst.STATE_NAME,
PM.GSTREGNO,
m.IM_MATCL,
cl.MATCL_DESC as RGC
from trinvs t
left join imtrans i on t.doc_type = i.doc_type and t.doc_no = i.doc_no
left join partymst pm on t.party_st = pm.party_st and t.party_no = pm.PARTY_NO
left join maitemh m on i.im_code = m.im_code
left join conact c on c.ACC_TYPE = pm.ACC_TYPE and c.PARTY_ST = pm.PARTY_ST
left join imcntrl cl on cl.IM_MATCL = m.IM_MATCL
LEFT JOIN ORDTERMS ON T.RDOC_NO = ORDTERMS.DOC_NO AND T.RDOC_TYPE = ORDTERMS.DOC_TYPE
LEFT JOIN PARTYMST TP ON T.TRNP_ST = TP.PARTY_ST AND T.TRNP_NO = TP.PARTY_NO
LEFT JOIN TRINVS T49 ON T.RDOC_NO = T49.DOC_NO AND T.RDOC_TYPE = T49.DOC_TYPE
LEFT JOIN TRINVSA TA ON T.DOC_TYPE = TA.DOC_TYPE AND T.DOC_NO = TA.DOC_NO
LEFT JOIN partyloc l1 on t.party_st = l1.party_st AND t.party_no = l1.party_no AND t.party_bloc = l1.party_bloc
LEFT JOIN state_std SST ON PM.state_code = SST.State_Code
LEFT JOIN STATE_STD SHIPST ON L1.STATE_CODE = SHIPST.State_Code
left join saletarget st on st.plantid = left(t.dc,1) and st.monthid=MONTH(getdate())
where

(LTRIM(T.DOC_TYPE) IN ('46','27') AND LEFT(M.FG_GRP,2) IN ('PR','BP') AND LEFT(T.DOC_NO,5) NOT IN ('GSAL2','GSALO') ) AND I.IM_QTY <> 0 and (MONTH(t.DOC_DATE) = MONTH(getdate()) AND YEAR(t.DOC_DATE) = YEAR(getdate()) )
--and left(t.dc,1) IN ('U','G','V')
and pm.PARTY_NO not in ('R0200','R0156','R0198','R0138','R0207','R0141','R0206','GW033','R0277','R0294','R0296','R0302','R0311','S2056','L0056','T0223','S1987','G0239')
group by left(t.dc,1),
t.party_no,
pm.PARTY_NAME,
pm.CICS,
PM.PARTY_CAT,
pm.OUR_CODE,
i.IM_CODE,
m.im_descr,
c.type_desc,
t.doc_no,
t.doc_date,
i.im_salert,
TA.TO_DEST,
PM.state_code,
sst.STATE_NAME,
PM.GSTREGNO,
m.IM_MATCL,
cl.MATCL_DESC

UNION

select
case when left(t.dc,1) = 'U' then 'Pantr'
when left(t.dc,1) = 'V' then 'Vir'
when left(t.dc,1) = 'G' then 'Gok'
when left(t.dc,1) = 'H' then 'HO'
when left(t.dc,1) = 'B' then 'Bhi'
when left(t.dc,1) = 'M' then 'Mu'
when left(t.dc,1) = 'O' then 'occ' else ' ' end as location,max(st.target) as saletarget,max(st.volume) as volume,
case when left(t.dc,1) = 'U' then 909.2
when left(t.dc,1) = 'V' then 131.1
when left(t.dc,1) = 'G' then 787.7
else 0 end as sale_target,
c.type_desc as territory,

t.doc_no as invoiceno, //*****this field is like 'AB0000123', 'AB0000124', 'AB0000125' i want to use this field for incremental load with last 4 digits(Right)******//

t.doc_date as doc_date,
t.party_no,
cast(pm.PARTY_NAME as varchar(60)) as party_name,
pm.cics,
CASE WHEN PM.PARTY_CAT = '1' THEN 'FOOD' WHEN PM.PARTY_CAT = '2' THEN 'PHARMA' WHEN PM.PARTY_CAT = '3' THEN 'FEED'
WHEN PM.PARTY_CAT = '4' THEN 'INDUSTRY' ELSE pm.OUR_CODE END AS segment,
max(i.IM_CODE) as im_code,
cast(max(m.im_descr) as varchar(50)) as im_descr,
sum(i.im_qty) qty,
i.im_salert as Rate,
sum(i.IM_BASIC) as amount,
sum(i.IM_BASIC)/10000000 as Total_IN_CR,
sum(i.doc_tax0) as Dis,
sum(i.doc_tax1) as Frt,
sum(i.doc_tax3) as Excise,
sum(i.doc_tax20) as IGST,
sum(i.doc_tax21) as CGST,
sum(i.doc_tax22) as SGST,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22) as Total,
sum(i.im_basic+i.DOC_TAX0+i.DOC_TAX1+i.DOC_TAX2+i.doc_tax3+i.DOC_TAX20+i.doc_tax21+i.doc_tax22)/10000000 as TotalINCR,
max(ordterms.FRT_DSC) as frtdescr,
MAX(T.DOC_TAX6) AS TCS,
MAX(t.DOC_TAX7) AS FRIEGHT,
max(pm.party_grp) as partygroup,
max(tp.party_no) as Trans_Code,
cast(max(tp.PARTY_NAME) as varchar(50)) as transporter,
TA.TO_DEST as to_dest,
max(T.rdoc_no) dino, max(t49.doc_date) as didate, PM.state_code,sst.STATE_NAME,PM.GSTREGNO,m.IM_MATCL,cl.MATCL_DESC as RGC
from trinvs t
left join imtrans i on t.doc_type = i.doc_type and t.doc_no = i.doc_no
left join partymst pm on t.party_st = pm.party_st and t.party_no = pm.PARTY_NO
left join maitemh m on i.im_code = m.im_code
left join conact c on c.ACC_TYPE = pm.ACC_TYPE and c.PARTY_ST = pm.PARTY_ST
left join imcntrl cl on cl.IM_MATCL = m.IM_MATCL
LEFT JOIN ORDTERMS ON T.RDOC_NO = ORDTERMS.DOC_NO AND T.RDOC_TYPE = ORDTERMS.DOC_TYPE
LEFT JOIN PARTYMST TP ON T.TRNP_ST = TP.PARTY_ST AND T.TRNP_NO = TP.PARTY_NO
LEFT JOIN TRINVS T49 ON T.RDOC_NO = T49.DOC_NO AND T.RDOC_TYPE = T49.DOC_TYPE
LEFT JOIN TRINVSA TA ON T.DOC_TYPE = TA.DOC_TYPE AND T.DOC_NO = TA.DOC_NO
LEFT JOIN partyloc l1 on t.party_st = l1.party_st AND t.party_no = l1.party_no AND t.party_bloc = l1.party_bloc
LEFT JOIN state_std SST ON PM.state_code = SST.State_Code
LEFT JOIN STATE_STD SHIPST ON L1.STATE_CODE = SHIPST.State_Code
left join saletarget st on st.plantid = left(t.dc,1) and st.monthid=MONTH(getdate())
where //(RIGHT(t.doc_no,4))< $(vMaxNo) AND
(LTRIM(T.DOC_TYPE) IN ('46','27') AND LEFT(M.FG_GRP,2) IN ('PR','BP') AND LEFT(T.DOC_NO,5) NOT IN ('GSAL2','GSALO')) AND I.IM_QTY <> 0 and (MONTH(t.DOC_DATE) < MONTH(getdate()) AND YEAR(t.DOC_DATE) = YEAR(getdate()) ) //t.DOC_DATE between '01-apr-2019' and '31-jan-2020'
--and left(t.dc,1) IN ('U','G','V')
and pm.PARTY_NO not in ('R0200','R0156','R0198','R0138','R0207','R0141','R0206','GW033','R0277','R0294','R0296','R0302','R0311','S2056','L0056','T0223','S1987','G0239')
group by left(t.dc,1),t.party_no,pm.PARTY_NAME,pm.CICS,PM.PARTY_CAT,pm.OUR_CODE,i.IM_CODE,m.im_descr,
c.type_desc,t.doc_no,t.doc_date,i.im_salert,TA.TO_DEST,PM.state_code,sst.STATE_NAME,PM.GSTREGNO,m.IM_MATCL,cl.MATCL_DESC
order by t.doc_date,t.doc_no desc;

//****SQL DATA END****//

//****STORE IN QVD****//

store $(name_app) into '$(qvd_1)'(qvd);