?
Solved

SQL If Statements

Posted on 2009-02-10
23
Medium Priority
?
384 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

0
Comment
Question by:r270ba
  • 11
  • 8
  • 2
  • +1
22 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23601130
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?
0
 

Author Comment

by:r270ba
ID: 23601178
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601181
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)


0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:r270ba
ID: 23601196
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

0
 

Author Comment

by:r270ba
ID: 23601277
@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?
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601351
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.
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601364
^^ explination, use the case to select what criteria you want for the column.  Do the join on all tables regardless of orignumb.
0
 

Author Comment

by:r270ba
ID: 23601368
@ged325

Can you give me a little more detail?  I am not that great with SQL :).
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601416
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
0
 
LVL 25

Expert Comment

by:reb73
ID: 23601417
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..
0
 

Author Comment

by:r270ba
ID: 23601484
@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

0
 

Author Comment

by:r270ba
ID: 23601503
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

0
 

Author Comment

by:r270ba
ID: 23601535
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601592
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601607
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

0
 

Author Comment

by:r270ba
ID: 23601625
@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
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601741
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

0
 

Author Comment

by:r270ba
ID: 23601753
@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.
0
 

Author Comment

by:r270ba
ID: 23601797
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23601813
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

0
 

Accepted Solution

by:
r270ba earned 0 total points
ID: 25225318
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 25227209
Let me know if you still looking for solution.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question