April 19, 2012 at 8:41 am
Hi all,
I have a table that stores Purchase Order information. I want check the log for PO and item /sku. The PO does not exists then insert. But, if the PO exists but not the sku then Insert the sku record.
If not exists
(select r.itnbr, r.ordno
from recvLogTest as r, recvTemp as t
where r.ordno = t.ordno and t.ordno = 'P046354')
Begin
insert into recvLogTest
(ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate)
select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate
from recvTemp
where ordno = 'P046354'
END
ELSE
IF not EXISTS
(select r.itnbr, r.ordno
from recvLogTest as r, recvTemp as t
where r.ordno = t.ordno and t.ordno = 'P046354' and r.itnbr not in (select itnbr from recvTemp where ordno = 'P046354' and itnbr = 'PH01340'))
Begin
insert into recvLogTest
(ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate)
select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate
from recvTemp
where ordno = 'P046354' and itnbr = 'PH01340'
END
The individual item will not load. Any help?
April 19, 2012 at 8:47 am
Not enough information. Please read the first article I reference below in my signature block and follow the instructions on what and how to post the information we need to best help you.
Also, when you say it doesn't work, not much to go on. Are you getting an error message, and if so what is the full error message?
April 19, 2012 at 9:21 am
Lynn,
Thanks for your help. I came up with a solution.
insert into recvLogTest
(ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate)
select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate
from recvTemp t
where t.ordno = 'P046354' and not exists(select *
from recvLogTest r
where t.ordno = r.ordno and t.itnbr = r.itnbr)
Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply