Sub-Select in FROM clause
-
I need to migrate views from Oracle to SQL Server. Oracle allows the following type of view to be created with a sub-select:
create view TestView
(MyColumn)
as select
Column1
from
(select Column1 from Table1)
SQL Server does not appear to allow sub-selects in the FROM clause. Is there some syntax is SQL Server that will allow this?
Thank you,
Bob
-
Ummm ... maybe you're missing something.
I have a couple of views defined that use a select statement in the FROM clause.
CREATE VIEW dbo.vw_AsxRptCosts
AS
SELECT
UserID
, ItemMonth
, ItemYear
, SUM(
CASE
WHEN Items = 1 THEN 0.01
ELSE 0
END
) as SingleCost
, SUM(
CASE
WHEN Utems > 1 THEN 0.023
ELSE 0
END
) as MultiCost
FROM (
SELECT
UserID
, Guid
, MONTH(DateTime) AS ItemMonth
, YEAR(DateTime) AS ItemYear
, COUNT(Guid) as Items
FROM dbo.tblUserAsxLogs
WHERE UserID <> 0
AND DATEPART(hh, DateTime) BETWEEN 9 AND 17
AND DATEPART(dw, DateTime) BETWEEN 2 AND 6
GROUP BY
UserID
, Guid
, MONTH(DateTime)
, YEAR(DateTime)
) as RawData
GROUP BY
UserID
, ItemMonth
, ItemYearHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
This one caught me for a while also... The key is to follow the subselect with AS <name>
SELECT myCol from ( select myCol from aTable) AS table
Guarddata-
-
I found late last night that SQL Server requires the table alias.
Thank you both!
Bob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply