Solved

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

Posted on 2014-01-29
5
303 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 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Insert data into database 2 37
Optimizing a query 3 34
My Query is not giving correct result. Please help 5 32
Extract string portion 2 16
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now