asked on

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

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:

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

snoyes_jw

In MySQL, it would be:

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

Open in new window

gregholl

ASKER

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:

Kevin Cross

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).

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregholl

ASKER

You rock Kevin! Thanks again!