We help IT Professionals succeed at work.

SQL If Statements

Medium Priority
395 Views
Last Modified: 2012-06-27
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?


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

Open in new window

Comment
Watch Question

Commented:
A few questions -

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?

Author

Commented:
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.
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

Open in new window

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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)


Author

Commented:
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)
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          	                                                             	                     

Open in new window

Author

Commented:
@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?
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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.
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
^^ explination, use the case to select what criteria you want for the column.  Do the join on all tables regardless of orignumb.

Author

Commented:
@ged325

Can you give me a little more detail?  I am not that great with SQL :).
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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

Commented:
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..

Author

Commented:
@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
 
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

Open in new window

Author

Commented:
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

Open in new window

Author

Commented:
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)
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

Open in new window

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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
 

Open in new window

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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
 

Open in new window

Author

Commented:
@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
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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>
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

Open in new window

Author

Commented:
@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.

Author

Commented:
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.



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

Open in new window

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
sorry, soptype isn't in your query (don't have the tables so couldn't test.)  

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

Open in new window

Commented:
I apologize that I have let this drag out for so long.  When executing the query above I received this error message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "B.sopnumbe" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "b2.sopnumbe" could not be bound.

I ended up going a completely different direction to get the results I needed.  I didn't receive a correct answer to the original question but don't mind splitting the points for their efforts.  I can't, however, mark any of the questions as correct.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
SharathData Engineer
CERTIFIED EXPERT

Commented:
Let me know if you still looking for solution.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.