Solved

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 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

8 Experts available now in Live!

Get 1:1 Help Now