Re: Nested select
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: Fri, 10 Mar 2006 22:05:19 -0000
"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E00259AC-FF4E-44D6-8730-5800CFDAC22A@xxxxxxxxxxxxxxxx
Can you tell me whats wrong with this query?
select count(*) as CountedOrders
from
(
select distinct [order]
from [OrdersTable]
where
[Customer]='100000' and
[Order Date] between '01/01/2005' and '31/12/2005'
)
It runs perfect in MS Access but not in MS SQL-Server.
I need to create a stored procedure that returns the number of orders from
a
specific customer on a specific period.
Thanks in advance for your help.
SQL requires an alias for the derived table. Also ORDER has to be delimited
because it's a keyword (and therefore not a good choice for a column name).
It's also good practice to use a locale-independent date format like I have
done below.
SELECT COUNT(*) AS countedorders
FROM
(SELECT DISTINCT [order]
FROM OrdersTable
WHERE customer='100000'
AND [order date] BETWEEN '20050101' AND '20051231'
) AS T ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
.
- Follow-Ups:
- Re: Nested select
- From: Hugo Kornelis
- Re: Nested select
- Prev by Date: Re: SQL
- Next by Date: Re: Nested select
- Previous by thread: Re: SQL
- Next by thread: Re: Nested select
- Index(es):
Relevant Pages
|
|
