?
Solved

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

Posted on 2014-01-29
5
Medium Priority
?
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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

718 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