I need some help with a SQL statement. I have a query that returns a set of results. I would like to say if one of the fields in that result set contains information, to run another query and return a different result set for that one row.
What is happening is that our order processing system will split sales orders if there is a backorder or a different warehouse and item is coming out of. I am trying to send an order acknowledgment to the customer but I want to send the original order and not 2 split orders.
I know that an order has a "master" order if the column orignumb is populated. Therefore:
if select orignumb is not null
then select order information for original order
select order information from current order
Below is my actual query that I need to have the if else statements added to. This will return the result set for the sales order (regardless of whether it is original or not). Basically, if orignumb contains info then I need to change the tables to (SOP30200) A and (SOP30300) B.
Does this make sense?
SELECT * FROM
(SELECT soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop10100) A
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B
ON a.sopnumbe = b.sopnumbe
(SELECT custnmbr, cntcprsn, userdef2 FROM rm00101) C
ON a.custnmbr = c.custnmbr
WHERE a.soptype='2' and a.bachnumb='PICK'
ORDER BY a.docdate ASC