r270ba
asked on
SQL If Statements
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:
(pseudo)
if select orignumb is not null
then select order information for original order
else
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?
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:
(pseudo)
if select orignumb is not null
then select order information for original order
else
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
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
ASKER
1. the field will be in the first select (A section) and the name is orignumb. I do not think that I have it included in the query I wrote. I will post an updated query with orignumb being selected as well. Sorry!
2. Actually, for my purposes, it is determine by whether or not there is anything listed in the orignumb field. So, if I select these rows and orignumb contains a value I want to run the SQL statement with SOP30200 and SOP30300 where sopnumbe=orignumb instead of just running it against SOP10100 and SOP30300 where bachnumb='pick'.
3. I will attach it in the next post by iteself.
2. Actually, for my purposes, it is determine by whether or not there is anything listed in the orignumb field. So, if I select these rows and orignumb contains a value I want to run the SQL statement with SOP30200 and SOP30300 where sopnumbe=orignumb instead of just running it against SOP10100 and SOP30300 where bachnumb='pick'.
3. I will attach it in the next post by iteself.
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop10100) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
declare @query varchar(8000)
set @query = your query (modify the ' to be '')
2 difference with your query.
use generic templates like DatabaseA and DatabaseB
if select orignumb is not null
begin
replace (@query, 'DatabaseA', 'YourDatabase')
replace (@query, 'DatabaseB', 'YourOtherDatabase')
end
else
begin
replace (@query, 'DatabaseA', 'SomeDatabase')
replace (@query, 'DatabaseB', 'SomeOtherDatabase')
end
exec(@query)
set @query = your query (modify the ' to be '')
2 difference with your query.
use generic templates like DatabaseA and DatabaseB
if select orignumb is not null
begin
replace (@query, 'DatabaseA', 'YourDatabase')
replace (@query, 'DatabaseB', 'YourOtherDatabase')
end
else
begin
replace (@query, 'DatabaseA', 'SomeDatabase')
replace (@query, 'DatabaseB', 'SomeOtherDatabase')
end
exec(@query)
ASKER
Ok so here are the results that are returned when I run the query.
If you notice, the first row does not have orignumb populated (which basically tells me it is the original order) but the second result does have orignum (which tells me that there is a "master" order)
If you notice, the first row does not have orignumb populated (which basically tells me it is the original order) but the second result does have orignum (which tells me that there is a "master" order)
orignumb soptype sopnumbe docdate custname custnmbr cstponbr shiptoname address1 city STATE zipcode shipmthd subtotal taxamnt docamnt user2ent bachnumb lnitmseq sopnumbe itemnmbr itemdesc dropship uofm locncode unitprce xtndprce quantity shipmthd custnmbr cntcprsn userdef2
2 SHP003736 2009-01-12 00:00:00.000 Hasentree Club, Inc. HAS01 VERBAL CRAIG Hasentree Club, Inc. 7328 Hasentree Way Wake Forest NC 27587 UPS GROUND 61.54000 4.16000 65.70000 bsatterfield1 PICK 16384 SHP003736 KNB-17A 10Hr Battery TK380 Kenwood 0 EA TSP 61.53850 61.54000 1.00000 UPS GROUND HAS01 Craig Dejong or Candace POM CUSTOMER 2008
PRT003845 2 PRT003845.1 2009-01-13 00:00:00.000 Reserve Club at Woodside Plantation WOO03 CAS-02989/TONY P. Reserve Club at Woodside Plantation Attn: Maintenance Shop Aiken SC 29803 UPS GROUND 35.33000 2.48000 37.81000 bwalker PICK 49152 PRT003845.1 2501151.JTE DISC 0 EA TSP 35.33140 35.33000 1.00000 UPS GROUND WOO03
ASKER
@ged325
When I try and populate @query I receive the following error:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am assuming because it cannot put more than one value for @query. Shouldnt that be in some type of while loop?
When I try and populate @query I receive the following error:
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am assuming because it cannot put more than one value for @query. Shouldnt that be in some type of while loop?
Didn't realize what you were trying to do.
Do this in your query:
join all tables regardless of
A, B = data when orignumb is null
C, D = data when not null
case when orignumb is null A.Value else C.Value as ValueName.
Do this in your query:
join all tables regardless of
A, B = data when orignumb is null
C, D = data when not null
case when orignumb is null A.Value else C.Value as ValueName.
^^ explination, use the case to select what criteria you want for the column. Do the join on all tables regardless of orignumb.
ASKER
@ged325
Can you give me a little more detail? I am not that great with SQL :).
Can you give me a little more detail? I am not that great with SQL :).
You have four seperate tables broken into pairs . . . 2 if orignumb is null and 2 if not. I'm assuming these are almost identical in type or the data you want to present.
Instead of just grabbing the 2 tables, grab all 4 of them. (Modify your join so that all 4 are returned)
Then in your select, you use a case statement to determine which of the 2 pairs you're going to use for that row.
http://www.tizag.com/sqlTutorial/sqlcase.php
Instead of just grabbing the 2 tables, grab all 4 of them. (Modify your join so that all 4 are returned)
Then in your select, you use a case statement to determine which of the 2 pairs you're going to use for that row.
http://www.tizag.com/sqlTutorial/sqlcase.php
r270ba -
Can you code the two queries separately and list it here, we can then assist in building either a IF ELSE segment or merge the two queries to dynamically generate the results..
Can you code the two queries separately and list it here, we can then assist in building either a IF ELSE segment or merge the two queries to dynamically generate the results..
ASKER
@reb73
Attached below are the 2 separate statements.
The first query is what I want to execute if orignumb is null
The second query is what I want to execute if orignumb is not null
Notice, they are the same exact table structure but the first is pulling data from SOP10100 and SOP10200 and the second is pulling data from SOP30200 and SOP30300.
The idea is that SOP10100 and SOP10200 are the "live" orders tables and SOP30200 and SOP30300 are the "history" orders tables. When a document is split, it moves the "master" document to history (SOP30200 and SOP30300) and creates the new split orders in the "live" tables (SOP10100 and SOP10200)
Attached below are the 2 separate statements.
The first query is what I want to execute if orignumb is null
The second query is what I want to execute if orignumb is not null
Notice, they are the same exact table structure but the first is pulling data from SOP10100 and SOP10200 and the second is pulling data from SOP30200 and SOP30300.
The idea is that SOP10100 and SOP10200 are the "live" orders tables and SOP30200 and SOP30300 are the "history" orders tables. When a document is split, it moves the "master" document to history (SOP30200 and SOP30300) and creates the new split orders in the "live" tables (SOP10100 and SOP10200)
This is for orignumb is null
orignumb soptype sopnumbe docdate custname custnmbr cstponbr shiptoname address1 city STATE zipcode shipmthd subtotal taxamnt docamnt user2ent bachnumb lnitmseq sopnumbe itemnmbr itemdesc dropship uofm locncode unitprce xtndprce quantity shipmthd custnmbr cntcprsn userdef2
2 SHP003736 2009-01-12 00:00:00.000 Hasentree Club, Inc. HAS01 VERBAL CRAIG Hasentree Club, Inc. 7328 Hasentree Way Wake Forest NC 27587 UPS GROUND 61.54000 4.16000 65.70000 bsatterfield1 PICK 16384 SHP003736 KNB-17A 10Hr Battery TK380 Kenwood 0 EA TSP 61.53850 61.54000 1.00000 UPS GROUND HAS01 Craig Dejong or Candace POM CUSTOMER 2008
PRT003845 2 PRT003845.1 2009-01-13 00:00:00.000 Reserve Club at Woodside Plantation WOO03 CAS-02989/TONY P. Reserve Club at Woodside Plantation Attn: Maintenance Shop Aiken SC 29803 UPS GROUND 35.33000 2.48000 37.81000 bwalker PICK 49152 PRT003845.1 2501151.JTE DISC 0 EA TSP 35.33140 35.33000 1.00000 UPS GROUND WOO03
==================================================================
This is for orignumb is not null
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
ASKER
Sorry...ignore the code attached above. Pasted the wrong first query.
This is for orignumb is null
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
=====================================================================
This is for orignumb is not null
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
ASKER
Man...I am really sorry. I am having issues with my keyboard and mouse today!!!! Please ignore the last 2 threads. I will make sure this is posted correctly this time.
*copy and paste from 2 threads ago*
@reb73
Attached below are the 2 separate statements.
The first query is what I want to execute if orignumb is null
The second query is what I want to execute if orignumb is not null
Notice, they are the same exact table structure but the first is pulling data from SOP10100 and SOP10200 and the second is pulling data from SOP30200 and SOP30300.
The idea is that SOP10100 and SOP10200 are the "live" orders tables and SOP30200 and SOP30300 are the "history" orders tables. When a document is split, it moves the "master" document to history (SOP30200 and SOP30300) and creates the new split orders in the "live" tables (SOP10100 and SOP10200)
*copy and paste from 2 threads ago*
@reb73
Attached below are the 2 separate statements.
The first query is what I want to execute if orignumb is null
The second query is what I want to execute if orignumb is not null
Notice, they are the same exact table structure but the first is pulling data from SOP10100 and SOP10200 and the second is pulling data from SOP30200 and SOP30300.
The idea is that SOP10100 and SOP10200 are the "live" orders tables and SOP30200 and SOP30300 are the "history" orders tables. When a document is split, it moves the "master" document to history (SOP30200 and SOP30300) and creates the new split orders in the "live" tables (SOP10100 and SOP10200)
This is for orignumb is null
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop10100) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
======================================================================
This is for orignumb is not null
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
modify query to be this:
SELECT * FROM
(SELECT orignumb, case when orignumb is null b1.soptype else b2.soptype end as soptype, case when orignumb is null b1.sopnumbe else b2.sopnumbe end as sopnumbe,
-- REPEAT FOR ALL FIELDS in B1
--case when orignumb is null b1.<col> else b2.<col> end as <col>
docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B1
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B2
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(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
needed to modify the join.
SELECT * FROM
(SELECT orignumb, case when orignumb is null b1.soptype else b2.soptype end as soptype, case when orignumb is null b1.sopnumbe else b2.sopnumbe end as sopnumbe,
-- REPEAT FOR ALL FIELDS in B1
--case when orignumb is null b1.<col> else b2.<col> end as <col>
docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B1
ON a.sopnumbe = b1.sopnumbe
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B2
ON a.sopnumbe = b2.sopnumbe
LEFT JOIN
(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
ASKER
@ged325
Error message:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'b1'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'B1'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'B2'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'C'.
When I execute that query
Error message:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'b1'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'B1'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'B2'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'C'.
When I execute that query
sorry, had the syntax wrong. This will work for the soptype only. Mimic for the rest.
CASE orignumb WHEN NULL then B.<col> ELSE b2.<col> END AS <col>
CASE orignumb WHEN NULL then B.<col> ELSE b2.<col> END AS <col>
SELECT * FROM
(SELECT orignumb, CASE orignumb WHEN NULL then B.soptype ELSE b2.soptype END AS soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B2
ON a.sopnumbe = b2.sopnumbe
LEFT JOIN
(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
ASKER
@ged325
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.soptype" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "b2.soptype" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.soptype" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "b2.soptype" could not be bound.
ASKER
One other thing I just thought about...
the second query should not be looking where soptype='2' and bachnumb='pick' because we have already identified the order number (sopnumbe). The select statement should end like this.
the second query should not be looking where soptype='2' and bachnumb='pick' because we have already identified the order number (sopnumbe). The select statement should end like this.
SELECT * FROM
(SELECT orignumb, soptype, sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(SELECT custnmbr, cntcprsn, userdef2 FROM rm00101) C
ON a.custnmbr = c.custnmbr
WHERE a.sopnumbe=a.orignumb
ORDER BY a.docdate ASC
sorry, soptype isn't in your query (don't have the tables so couldn't test.)
Undo soptype and do sopnumbe instead:
Undo soptype and do sopnumbe instead:
SELECT * FROM
(SELECT orignumb, soptype, CASE orignumb WHEN NULL then B.sopnumbe ELSE b2.sopnumbe END AS sopnumbe, docdate, custname, custnmbr, cstponbr, shiptoname, address1, city, STATE, zipcode, shipmthd, subtotal, taxamnt, docamnt, user2ent, bachnumb FROM sop30200) A
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop30300) B
ON a.sopnumbe = b.sopnumbe
LEFT JOIN
(SELECT lnitmseq, sopnumbe, itemnmbr, itemdesc, dropship, uofm, locncode, unitprce, xtndprce, quantity, shipmthd FROM sop10200) B2
ON a.sopnumbe = b2.sopnumbe
LEFT JOIN
(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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Let me know if you still looking for solution.
1. what field/table is the 'orignumb' populated in? Is this sopnumbe
2. What determines whether an order is a 'master' order or a 'backorder'? Is the soptype??
3. Can you list the current output for a few sample records?