Knowledgebase
Queries to fix BPRO database stocktake problems
Posted by Lloyd Jansen on 02-07-2014 01:31 PM

Backup all tables

select * into s_take_2014 from s_take
;
select * into stock_new2014 from stock
;
Select * into s_take_old2014 from s_take_old

 

Check for duplicate product numbers and barcodes

select ccstock.prod_no as ccprod_no,

llstock.prod_no as llprodno,
llstock.barcode1 as llb1,
llstock.barcode2 as llb2,
llstock.barcode3 as llb3,
llstock.servicei as llserv
from stock ccstock, stock llstock
where ((ccstock.prod_no = llstock.barcode1)
or (ccstock.prod_no = llstock.barcode2)
or (ccstock.prod_no = llstock.barcode3))
and (ccstock.prod_no != llstock.prod_no)
group by ccstock.prod_no, llstock.prod_no,
llstock.barcode1, llstock.barcode2,
llstock.barcode3, llstock.servicei
order by ccstock.prod_no

 

/*
update STOCK set Barcode1 = ''
;
update STOCK set Barcode2 = ''
;
update STOCK set Barcode3 = ''
*/

 

Check to see if barcode is blanked

select * from stock_new2014
;
select * from s_take_old2014
;

CHeck for duplicates in the stocktake table

select prod_no, count(*)
from s_take
where store_id = 'DCC'
group by prod_no
having count(*) > 1

 

 

Put barcodes back into the stock table.

/*

update stock
SET stock.barcode2 = stock_new2014.barcode1
from stock, stock_new2014
where stock.prod_no=stock_new2014.prod_no

update stock
SET stock.barcode2 = stock_new2014.barcode2
from stock, stock_new2014
where stock.prod_no=stock_new2014.prod_no

update stock
SET stock.barcode2 = stock_new2014.barcode3
from stock, stock_new2014
where stock.prod_no=stock_new2014.prod_no

*/

 

 /*
select st.prod_no, s.prod_no, st.qty, s.stock_hand
from s_take_2014 st, stock s
where st.prod_no = s.prod_no
and st.prod_no <> s.prod_no
*/

 

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: