Transact SQL Server 2005 "Error"

[chillay]

I am using "Microsoft SQL Server Management Studio Express 2005"

- I am running the following Transact SQL Query:


SELECT Customer.FirstName, Customer.LastName, Customer.HomePhone,
(SELECT MAX(Orders.OrderDate)
FROM Orders
WHERE Orders.CustomerID = Customer.CustomerID) AS LastOrderDate
FROM Customer


- I get the following error:

Major Error 0x80040E14, Minor Error 25501
> SELECT Customer.FirstName, Customer.LastName, Customer.HomePhone,
(SELECT MAX(Orders.OrderDate)
FROM Orders
WHERE Orders.CustomerID = Customer.CustomerID) AS LastOrderDate
FROM Customer
There was an error parsing the query. [ Token line number = 2,Token line offset = 5,Token in error = SELECT ]

- Does anyone know what I am doing wrong? This query works fine in Microsoft Access Jet SQL.

Thank you,

Chillay

 

[AlexCuse]

I think you ought to try a join. Something like this perhaps?

[COLOR=blue]SELECT[/color] Customer.FirstName
, Customer.LastName
, Customer.HomePhone
, b.MaxDate [COLOR=blue]as[/color] LastOrderDate
[COLOR=blue]FROM[/color] Customer
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]SELECT[/color] CustomerID
,[COLOR=#FF00FF]MAX[/color](Orders.OrderDate) [COLOR=blue]as[/color] MaxDate
[COLOR=blue]FROM[/color] Orders
[COLOR=blue]group[/color] [COLOR=blue]by[/color] CustomerID
)  b
[COLOR=blue]on[/color] Customer.CustomerID = b.CustomerID

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[chillay]

For some reason I get the same error when I run your query Alex.

Thx

Chillay

 

[philhege]

Perhaps the schema are coming into play? What happens when you fully qualify the objects?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[chillay]

Following is the original qualified query that gives me the error:

SELECT Customer.FirstName, Customer.LastName, Customer.HomePhone,
(SELECT MAX(Orders.OrderDate)
FROM Orders
WHERE Orders.CustomerID = Customer.CustomerID) AS LastOrderDate
FROM Customer

Error: There was an error parsing the query. [ Token line number = 2,Token line offset = 5,Token in error = SELECT ]

Thank you

 

[gmmastros]

What version of Microsoft SQL Server are you using.

Select @@Version

Please post the output of the above statement.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

What version of SQL are you using?

Please run SELECT @@VERSION

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

George! I should have gone back and checked before hitting submit.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

nonetheless... it's still good advice.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

I'm thinking that the version is going to come back as SQL Ce. Which doesn't support sub-selects and derived tables.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

That's also what I was thinking.

chillay may want to try this query...

[COLOR=blue]SELECT[/color] Customer.CustomerId,
       Customer.FirstName, 
       Customer.LastName,
       Customer.HomePhone,
       [COLOR=#FF00FF]Max[/color](Orders.OrderDate) [COLOR=blue]as[/color] LastOrderDate
[COLOR=blue]FROM[/color]   Customer
       [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] Orders
         [COLOR=blue]on[/color] Customer.CustomerID = b.CustomerID
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Customer.CustomerId, 
       Customer.FirstName, 
       Customer.LastName,
       Customer.HomePhone

Notice that I added Customer.CustomerId in the list of fields to return. I did this because, technically, there could be duplicate customers with the same first name, last name, Home Phone combination. While unlikely to occur, it's possible. I assume that CustomerId is a unique identifier here, so including it in the select list and the group by clause will prevent incorrect data from returning. Also, note that it is not necessary to return CustomerId from the query (it can exist in the group by clause without returning it). I added it to the select list so that in the unlikely circumstance that there are duplicates, it will be obvious that this is really referring to another customer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chillay]

- When I run SELECT @@VERSION I get the following:

Major Error 0x80040E14, Minor Error 25623
> SELECT @@VERSION
The global variable name is not valid. [ Global variable Name = @@VERSION ]


- Following is my version information:

SQL Server Compact Edition
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) .6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16386
Microsoft .NET Framework 2.0.50727.312
Operating System 6.0.6000


- gmmastros's program works but I need my subquery to run.

Thank you to everyone.

Chillay

 

[AlexCuse]

gmmastros's program works but I need my subquery to run.

I'm thinking that the version is going to come back as SQL Ce. Which doesn't support sub-selects and derived tables.

So, it does not look like your subquery will run. Why do you need to get it to run? George's query does more or less the same thing as the query I posted above that uses the subquery. Is there a particular reason why you need your subquery to run? I am not so sure about SQL Compact Edition, but maybe you could create a view using your subquery (of course, this may not meet your requirement either)? If it is really that important, it sounds like your only option may be to install a different version of SQL Server.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[chillay]

I teach a course that supports several different DBMSs platforms. I often debug script for the students.

After buying a new computer with Vista my version of SQL Server no longer runs. I was able to download SQL Compact Edition and now I am in the process of testing all of the SQL script examples for the course.

The problem may be the version that I am using.

I must say though. You guys have been great. I appreciate the effort.

Thanks a bunch

Chillay

 

[AlexCuse]

Ah, Vista. Can't wait till we get it here.... NOT!

What version were you using before? Maybe you can download the normal SQL Express, I have used that and not had any troubles with subqueries. I don't even really know what Compact Edition is though , I could be way off base here...

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[chillay]

I was using SQL Server 7.0. Vista does not support it or the normal SQL Express.

I can not tell you how much software I had to repurchase due to Vista not supporting the software. My printer does not work with the current drivers and at one point I could not connect to the Internet until I updated my software with Verizon. I am having to replace most of the software I use.

I am going to try to work past the error by checking on-line concerning the error.

Thanks again!

Chillay

 

[ptheriault]

I would download SQL developer edition. There are no limitations in the code you can run.

I've worked with CE in the past. It's primarly used for mobile devices. It's not easy. I had to use RDA to send data to the primary database.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chillay]

Thanks. I will look into it.

I am looking for a version of SQL Server that is free with no expiration date since I am only using it to debug script.

CE seemed to be what I was looking for yet I have errors with nested subqueries.

Thanks again!

Chillay