[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

HELP with a MSSQL (or MySQL?) statement - Declare function

Posted on 2014-01-29
5
Medium Priority
?
337 Views
Last Modified: 2014-01-29
Basically we want to display certain data about all the orders on one particular batch. Here's what I've got

DECLARE @Batch INT
SELECT @Batch = 1223
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 works just nicely. I get no errors with the above code.

However, I don't want to have to manually change the parameter @Batch each time. (In the case above it was set at 1223)

All I really need is for the system to automatically select the highest batch number.

I tried to change line two to the following:
DECLARE @Batch INT
SELECT @Batch = (MAX(BatchNumber),0) FROM Orders;

But I get the following error:
1
I've also tried:
DECLARE @Batch INT
SELECT @Batch = COALESCE(MAX(BatchNumber),0)  FROM Orders;
still get an error.

FINALLY, I tried:
DECLARE @Batch INT
SELECT @Batch = (Select max(BatchNumber) FROM Orders);

All to no avail. What am I doing wrong?

This is an administrative page for a shopping cart app by Volusion.com I'm not sure if it's MSSQL or MySQL
0
Comment
Question by:gregholl
  • 2
  • 2
5 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 39818872
In MySQL, it would be:

SELECT @batch := MAX(BatchNumber) FROM Orders;

Open in new window

0
 
LVL 1

Author Comment

by:gregholl
ID: 39818928
OK. Here is the complete code:

DECLARE @Batch INT
SELECT @batch := MAX(BatchNumber) FROM Orders;
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


When I run it I get the following error:
1
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39819018
I think it definitely is MSSQL based on other question.
Therefore, your original query should work.  note the error says the issue is syntax near ','; hence, look in the code for an extra comma.

Looking at the below, I do not see a comma out of place; however, it could be something odd happening by one of the commas.  Therefore, to test you could comment out each of the columns, adding them back one at a time, so you can find the column that causes the query to break.
DECLARE @Batch INT
SELECT @Batch = 1223
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


EDIT: given some of the errors you have gotten with this admin page, I would also make sure it is not something like the "SELECT @Batch = 1223".  It may want you to use "SET @Batch = 1223" since there is no table involved.

However, I know you want this dynamic, so you can try with:
SELECT @Batch = MAX(BatchNumber) FROM Orders

Open in new window


As an aside, you can do this without the parameter by adding a JOIN or WHERE condition that filters the query to the MAX(BatchNumber).
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39819041
Here is an example of what I meant above:
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 (
    SELECT Batch = MAX(BatchNumber)
    FROM Orders
) B ON B.Batch = O.BatchNumber
JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Countries CT ON CT.Name = O.ShipCountry
ORDER BY O.OrderID ASC

Open in new window

0
 
LVL 1

Author Closing Comment

by:gregholl
ID: 39819274
You rock Kevin! Thanks again!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

872 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