Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Help with a MySQL statement:

Here is the statement:

SELECT Orders.OrderID, Orders.BatchNumber, Orders.Custom_Field_eBayID
FROM (Products INNER JOIN OrderDetails ON Products.ProductCode = OrderDetails.ProductCode) INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.ShipDate IS NULL AND Orders.BatchNumber IS NULL AND Orders.OrderStatus = 'PROCESSING' AND OrderDetails.QtyOnBackOrder <1

Open in new window


Here is a sample of the output I get:

1
I'm getting 2 problems.

Problem 1:
First of all, I only want to see each order number ONCE in the list. (I realise that these are showing multiple times because there are multiple order detail lines on that particular order.) So how do I fix that.

Problem 2:
I have the expression WHERE OrderDetails.QtyOnBackOrder <1
Basically I don't want to pull orders that have items backordered, since I can't ship it.
The problem is that some orders have zero as QtyOnBackOrder and some have NULL as QtyOnBackOrder. The ones with NULL are not showing in the list.  So how do I fix that.Thanks!
0
gregholl
Asked:
gregholl
  • 14
  • 11
  • 9
2 Solutions
 
Koen Van WielinkIT ConsultantCommented:
Problem 1: This depends on whether all the information you are pulling out can be consolidated for a single order. For example, you are also selecting column "batchnumber". If you can have more than 1 batch number for a single order number, you cannot prevent the order number from appearing more than once. For every batch number displayed it would repeat the order number. If however all the information selected is unique for a single order number, then you can use the "distinct" clause to remove duplicates (see code below).

Problem 2: Use the IFNULL clause to tell MySQL how to treat NULL values. By saying IFNULL(OrderDetails.qtyOnBackOrder,0) < 1 it will treat a NULL value as 0.

SELECT	distinct
			Orders.OrderID
		,	Orders.BatchNumber
		,	Orders.Custom_Field_eBayID
FROM	(Products 
		INNER JOIN OrderDetails 
			ON Products.ProductCode = OrderDetails.ProductCode) 
		INNER JOIN Orders 
			ON OrderDetails.OrderID = Orders.OrderID
WHERE	Orders.ShipDate IS NULL 
AND		Orders.BatchNumber IS NULL 
AND		Orders.OrderStatus = 'PROCESSING' 
AND		IFNULL(OrderDetails.QtyOnBackOrder,0) <1

Open in new window


I do apologise if there are syntax errors in the code. I don't think so, but I don't have a MySQL environment to test and I'm usually working with MSSQL. But the same logic applies :D.
0
 
greghollAuthor Commented:
Thanks dude! Looks great. I'll try this tomorrow am.

:D
0
 
greghollAuthor Commented:
P.S.

I thought I should mention. Maybe I should make this an UPDATE query. I'm very novice at all this. Have basic knowledge with SELECT query, and I've not yet delved in to UPDATE queries.

But.....

What we would like to happen, ideally is:

- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –

The results of the query above should actually be assigned a BatchNumber in it's given field. For example, say my next batch should be numbered 1212. It would be awesome if the query could assign that batch number to all the records that the query outputs. However, I have NO IDEA how to do that.

- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –

On a sidenote. Regarding your concern earlier:

If you can have more than 1 batch number for a single order number, you cannot prevent the order number from appearing more than once.

This would not be an issue. We don't ever assign one order to more than one batch.

- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –

Thanks again!! ¯\_(¿)_/¯
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Koen Van WielinkIT ConsultantCommented:
Can you give a sample output of how you expect that update to work? Should the batch number you see in your select statement be filled by something else, rather than be read from a table? How would you know what the next available batch number is?
0
 
Koen Van WielinkIT ConsultantCommented:
Also, when you expect this to be an update statement, what do you expect to update? Do you write this result into another table?
0
 
greghollAuthor Commented:
Here is a screenshot of what the output should look like:

1
Should the batch number you see in your select statement be filled by something else, rather than be read from a table?
For now, I can set the batch number manually each time before I run the query.

How would you know what the next available batch number is?
I would check manually

when you expect this to be an update statement, what do you expect to update? Do you write this result into another table?
The idea is to change the batch number from NULL (first screenshot from yesterday) to 1212 (above). It is the orders table that I am updating, in particular the "BatchNumber" field.

If I'm way off on left field, please pardon me, as I admit I don't know much about this.
0
 
greghollAuthor Commented:
Going back to the original question.

The following gave me a syntax error:
IFNULL(OrderDetails.QtyOnBackOrder,0) <1

But it worked fine when I put:
ISNULL(OrderDetails.QtyOnBackOrder,0) <1

So maybe my system is actually MSSQL? What do you think?
0
 
greghollAuthor Commented:
O boy.

I've noticed another issue with the original question.

Say a person buys the following items. These are the order details

OrderID: 2300

OrderDetails:
WIDGETA QtyOnBackorder = 0
WIDGETB QtyOnBackorder = 0
WIDGETC QtyOnBackorder = 0
WIDGETD QtyOnBackorder = 2

All the items are in stock, except for WidgetD

So my new syntax, with the "SELECT distinct" is great. BUT! The select query is going to return orderID 2300 no matter what.

So the idea is: Even if there is only one item on a order that can't be shipped, we hold the whole order.

Is there some way to fanangle the query to so that orders like 2300 are NOT in the output.

Basically, ever item detail on an order must have "QtyOnBackorder = 0" in order to get batched.

Or am I getting too complicated now. (¿¿)
0
 
Kevin CrossChief Technology OfficerCommented:
I would use NOT EXISTS, so you pull the orders that are ready to ship that do not have a single record not ready to ship.

...
AND NOT EXISTS (
    SELECT 1
    FROM OrderDetails lkup
    WHERE lkup.OrderID = OrderDetails.OrderID
    AND lkup.QtyOnBackorder > 0
)
...

Open in new window


In fact, if you do not need any of the columns from the OrderDetails, you could use EXISTS and NOT EXISTS conditions in the WHERE clause to avoid the use of DISTINCT in the first place.  In other words, you pull just the order header, which should be unique.

EDIT: here is a full example of what I mean with regard to the original query.
SELECT O.OrderID
     , O.BatchNumber
     , O.Custom_Field_eBayID
FROM Orders O
WHERE O.ShipDate IS NULL
  AND O.BatchNumber IS NULL 
  AND O.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = O.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window

0
 
Koen Van WielinkIT ConsultantCommented:
Ok, let me try to summarize first what you've got and what you're trying to achieve, to make sure I get it correctly.

1. The data you are analyzing are orders which are booked but not yet delivered.
2. It is known for each item on an order whether or not it is in stock.
3. If it is not in stock, or there is insufficient stock, the item gets a quantity on backorder > 0.
4. You are trying to extract data for all the orders that can be shipped (i.e. which have no backorders), and you wish to assign a sequencial batch number to all the orders that will be prepared at the same time.
5. This batch number should be entered into the Orders table for all the orders that are batched.

Now, assuming that I am right, and assuming that your batch number is always just the next following number after the previously used batch, if you are using MSSQL you could rewrite your query as follows:

set	@BatchNumber = (Select max(BatchNumber) + 1
from	Orders);


Update	Orders
Set		Batch_number = @BatchNumber
From	(Products p
		INNER JOIN OrderDetails od
			ON P.ProductCode = od.ProductCode) 
Where	Orders.OrderID = od.OrderID
and		Orders.ShipDate IS NULL 
AND		Orders.BatchNumber IS NULL 
AND		Orders.OrderStatus = 'PROCESSING' 
AND		not exists
		(Select	1
		from	OrderDetails od2
		Where	isnull(od2.QtyOnBackOrder,0) <1
		and		od.OrderID = od2.OrderID);

Open in new window



Unfortunately I think that the code in MySQL would be rather different. What program are you using to write all these queries? if it's MSSQL you'd probably know, as you'd be using the MSSQL Management Studio for this. Any screenshots?
0
 
Kevin CrossChief Technology OfficerCommented:
The syntax becomes easier to port between MSSQL and MySQL if you avoid the JOINs as I showed.  However, Guy wrote a nice summary of executing UPDATE with JOINs on the different database systems, including MySQL and MSSQL.

http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

@Kvwielink, your NOT EXISTS statement is backwards.  When checking for the existence of a record with back-ordered quantity, the NULLs will not matter since they represent 0.  Hence, it removes one of the differences between MSSQL and MySQL syntax.  Further, the other JOINs become unnecessary.

MSSQL:
UPDATE O 
SET O.BatchNumber = 'new batch number here'
FROM Orders O
WHERE O.ShipDate IS NULL
  AND O.BatchNumber IS NULL 
  AND O.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = O.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window


MySQL:
UPDATE Orders O 
SET O.BatchNumber = 'new batch number here'
WHERE O.ShipDate IS NULL
  AND O.BatchNumber IS NULL 
  AND O.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = O.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window


If you need the JOINs for some other reason, MySQL does the JOINs in the UPDATE portion of the statement (i.e., UPDATE TableA a JOIN TableB b ON a.ID = b.ID SET ...).

I hope that helps!
0
 
Koen Van WielinkIT ConsultantCommented:
Kevin's right about the not exist statement. My bad, it's getting late here... :p.
0
 
Kevin CrossChief Technology OfficerCommented:
*smile* It happens.  I have the fortune of being 12 hours behind, so just drinking my morning cup of coffee.  By the way, the screen shots do look like the results from SQL Management Studio versus MySQL Workbench — at least the defaults of MySQL Workbench do not display record number whereas SSMS does.  Hence, I think you are right on this being MSSQL.
0
 
Koen Van WielinkIT ConsultantCommented:
If you're referring to the screenshots in tbe first few posts, they look like Excel spreadsheets to me. SSMS does not use letters as column headers.
0
 
Kevin CrossChief Technology OfficerCommented:
Ha.  Yes.  I missed the letter headings.  However, given IFNULL did not work, I suspect your assessment that this is MSSQL still is correct.
0
 
greghollAuthor Commented:
Thanks guys. There is a lot of stuff to process here, and I'm trying to figure it out. Here is a screenshot of what I am working in:

1
0
 
Kevin CrossChief Technology OfficerCommented:
Hmm.  This is an administrative page for a shopping cart app, so some syntax may not work if the interface does not support it.  Therefore, you may have to walk slowly through steps.  Hence, start back with SELECT statement.

SELECT OrderID
     , BatchNumber
     , Custom_Field_eBayID
FROM Orders
WHERE ShipDate IS NULL
  AND BatchNumber IS NULL 
  AND OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails
      WHERE OrderDetails.OrderID = Orders.OrderID
      AND OrderDetails.QtyOnBackOrder > 0
  )
;

Open in new window


I dropped the aliases to make this simpler and to allow queries later that work with both MySQL and MSSQL without much work on your part.

Does the drop-down list for query type contain UPDATE?  If yes, try changing the query type after the SELECT works and see what the interface does automatically.  If nothing, then we can try an UPDATE syntax (without the aliases) that should work in both MySQL and MSSQL from there.

Specifically, this UPDATE statement should work in both SQL environments:
UPDATE Orders
SET BatchNumber = 'new batch number here'
WHERE ShipDate IS NULL
  AND BatchNumber IS NULL 
  AND OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails
      WHERE OrderDetails.OrderID = Orders.OrderID
      AND OrderDetails.QtyOnBackOrder > 0
  )
;

Open in new window

0
 
greghollAuthor Commented:
Dear Kvwielink:

Ok, let me try to summarize first what you've got and what you're trying to achieve, to make sure I get it correctly.

1. The data you are analyzing are orders which are booked but not yet delivered.
2. It is known for each item on an order whether or not it is in stock.
3. If it is not in stock, or there is insufficient stock, the item gets a quantity on backorder > 0.
4. You are trying to extract data for all the orders that can be shipped (i.e. which have no backorders), and you wish to assign a sequencial batch number to all the orders that will be prepared at the same time.
5. This batch number should be entered into the Orders table for all the orders that are batched.

Now, assuming that I am right, and assuming that your batch number is always just the next following number after the previously used batch, if you are using MSSQL you could rewrite your query as follows:

This is exactly what we are trying to accomplish. Down to the letter.

So I've entered the syntax from your post ID: 39817783

This is the error I am getting now:
1
0
 
Kevin CrossChief Technology OfficerCommented:
Because you are using an interface to SQL, I doubt you will be able to use multi-line (separated by semi-colons) statements.  I may be wrong, though.  If it will work, make sure you have the following two lines before your UPDATE.
DECLARE @BatchNumber AS INT;
SELECT @BatchNumber = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;

Open in new window

0
 
greghollAuthor Commented:
Kevin Cross
regarding POST ID: 39818252

Both our your queries worked just great.

1: The first (SELECT QUERY) indeed exported the outstanding orders to ship. All the numbers appear correct.

2: The second (UPDATE QUERY) indeed gave an error. However, it did work just fine.

Greg
0
 
Kevin CrossChief Technology OfficerCommented:
Greg, I am glad to hear the SELECT worked.  Regarding the second query, do you need help with the error?  You followed up with "it did work just fine," so making sure you do not need more assistance.

If so, please let us know.

Otherwise, best regards and happy coding,

Kevin
0
 
greghollAuthor Commented:
I've tried adding in:

From Post 39818297:
DECLARE @BatchNumber AS INT;
SELECT @BatchNumber = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;

And I am getting an error.

- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –
On another note, here is a similar query in my system that is working just fine for me:
DECLARE @Batch INT

SET @Batch = 1231

SELECT O.OrderID, O.ShipFirstName, O.ShipLastName, O.ShipAddress1, O.ShipAddress2, O.ShipCity, O.ShipState, O.ShipPostalCode, CT.Name AS Country, O.ShipPhoneNumber, O.ShippingMethodID, O.TotalShippingCost, (O.PaymentAmount - O.TotalShippingCost) AS FOB, O.OrderStatus, O.BatchNumber, O.Custom_Field_eBayID, C.EmailAddress
FROM Orders O
JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Countries CT ON CT.Name = O.ShipCountry
WHERE O.BatchNumber = @Batch
ORDER BY O.OrderID ASC

Open in new window


This is only for an example of how the "SET" and "SELECT" functions are indeed working in this other unrelated query.
- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –

So for now...

Kevin's  second suggestion in post #39818252 works just great. The caveat is that the system gives an error. But in spite of the error the batch numbers indeed get assigned correctly.

But it would be nice to go the next step
0
 
Kevin CrossChief Technology OfficerCommented:
Interesting that the parameter works for the SELECT but not on the UPDATE.
What is the error you get on my second query?  What is the next step?
0
 
greghollAuthor Commented:
Here is the entire code, as I understand it:

DECLARE @BatchNumber AS INT;
SELECT @BatchNumber = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;

UPDATE Orders
SET BatchNumber = @BatchNumber
WHERE ShipDate IS NULL
  AND BatchNumber IS NULL 
  AND OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails
      WHERE OrderDetails.OrderID = Orders.OrderID
      AND OrderDetails.QtyOnBackOrder > 0
  )
;

Open in new window


Here is the error I get:

1
"Next step" is just getting the whole thing to work. :D
0
 
greghollAuthor Commented:
YeaH! I've been fiddling and fiddling and I finally got it to work. Here is the final code, for posterity:

DECLARE @Batch AS INT;
SELECT @Batch = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;

UPDATE Orders
SET Orders.BatchNumber = @Batch
WHERE Orders.ShipDate IS NULL
  AND Orders.BatchNumber IS NULL 
  AND Orders.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = Orders.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window

0
 
greghollAuthor Commented:
Thanks guys!
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.  Very interesting it wanted you to use the two-part name.  Glad you found that.  It would not have been obvious since there is no ambiguity in the outer query where the columns are coming from.  

Best regards,

Kevin
0
 
Koen Van WielinkIT ConsultantCommented:
Glad to hear the problem is solved Greg. Just out of curiosity since it threw an error when you did not specify the table names, is there perhaps another "batchnumber" column in another table that is used in the queryt? In this case SQL would throw an error because it wouldn't know which table to update. It might then also mean you'd have to update another table at the same time.
0
 
greghollAuthor Commented:
Hi Kevin. I'm glad you wrote. Turns out I spoke too soon earlier:

The caveat is that the system gives an error. But in spite of the error the batch numbers indeed get assigned correctly.

Doh!

On second thought, the batch numbers aren't actually getting assigned properly.

Here is the error I get:

1
Here is my code:

DECLARE @Batch AS INT;
SELECT @Batch = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;

UPDATE Orders
SET Orders.BatchNumber = @Batch
WHERE Orders.ShipDate IS NULL
  AND Orders.BatchNumber IS NULL 
  AND Orders.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = Orders.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window


Should I try that suggestion of commenting out lines until I find the error? Does that work with an Update query?
0
 
Koen Van WielinkIT ConsultantCommented:
Hi Greg,

The syntax should be ok, so it might be an issue with the front end software that you're using. Kevin asked earlier if there was an "Update" option in the DB Query Type drop down list, but I don't think you answered that. If you set this to "select" but you're in fact running an update it might be the software's not too happy about that.
What are your options available in that drop down?

Rgds,

Koen
0
 
Koen Van WielinkIT ConsultantCommented:
Oh, and I wouldn't attempt commenting out lines in an update query. You might end up updating the wrong things.
Best thing would be if you'd have a test environment to play with, but I guess that's out of the question?
0
 
Koen Van WielinkIT ConsultantCommented:
Ok, to get rid of some of the uncertainty of which DB system to use, I contacted the supplier of your web store (Volution). They confirmed they use MSSQL, so that's the syntax we should be using.
I also saw on their website that in the query bank you can choose either a "select" query, or an "update" query, so be sure you have this set to "update". If it still doesn't work, try if you can change the table from DB_EXPORT to ORDERS, since that's the table you're trying to update.
The chat rep confirmed you can run update statements across multiple tables if you like, and  she was not aware of any limitations regarding the query bank tool to manipulate database data directly. So what you're trying to do should be possible.
0
 
greghollAuthor Commented:
THANKS Kvwielink

Indeed I have the "UPDATE" option selected.

See:
1
When I change DB_EXPORT to ORDERS, it disappears from my list of queries to run:
See:
2
Here is Volusion's help screen regarding what the "DB_EXPORT" is all about:
3
When I enter the code as follows, it does give me the same old error, but in spite of the error, the batch number of 1235 (as in the example) did indeed get assigned to the necessary orders.

UPDATE Orders
SET Orders.BatchNumber = '1235'
WHERE Orders.ShipDate IS NULL
  AND Orders.BatchNumber IS NULL 
  AND Orders.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = Orders.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window


So pretty much the problem is boiling down one of these  lines:

DECLARE @Batch AS INT;
SELECT @Batch = COALESCE(MAX(BatchNumber),0) + 1 FROM Orders;
SET Orders.BatchNumber = @Batch

which are the only lines that got changed or removed.
0
 
Koen Van WielinkIT ConsultantCommented:
Hi Greg,

Sorry for not replying sooner. There's one other thing you could try, since you're not processing records one at a time and want all the batch numbers to be the same for a single update:

UPDATE Orders
SET Orders.BatchNumber =  (Select COALESCE(MAX(BatchNumber),0) + 1 FROM Orders)
WHERE Orders.ShipDate IS NULL
  AND Orders.BatchNumber IS NULL 
  AND Orders.OrderStatus = 'PROCESSING' 	
  AND NOT EXISTS (
      SELECT 1
      FROM OrderDetails D
      WHERE D.OrderID = Orders.OrderID
      AND D.QtyOnBackOrder > 0
  )
;

Open in new window


However, since you're still getting errors, I'd advise you to contact the customer service of your webshop provider. Like I said, I contacted them to get the SQL version they're using and they were very helpful and appeared knowledgable. They told me that if the person you are talking to doesn't know the answer, they'll pass the problem on to someone who does.
Give them the queries we've been writing, and ask them why it's giving errors. The queries are correctly structured from an MSSQL point of view, so it must be something related to their front end tools.
Good luck, and let us know if you got a fix.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 14
  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now