Unit 5 Seminar. Derived Table. A derived table is a virtual table that’s calculated on the fly from a select statement.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Generate a report that shows off the total number of orders each customer placed in 1996
WITH emp (SELECT columnname
FROM tablenname AS column alias
INNER JOIN tablename as column alias
ON table.column = table.column)
You then can use the CTE in a query
FROM CTE AS column alias
This translates into two SQL queries - one that gets the "initial" data UNIONed with one that performs the recursion.
WITH SampleOrgChart (Level, Position, ReportingLevel, OrgLevel, SortKey)
AS ( -- Create the anchor query. This establishes the starting – point
SELECT a.LevelID, a.Position, a.ReportingLevelID, 0, CAST (a.LevelID AS VARBINARY(900))
FROM dbo.SampleOrg a
WHERE a.Position = 'Product Development Manager'
UNION ALL -- Create the recursive query. This query will be executed -- until it returns no more rows
SELECT a.LevelID, a.Position, a.ReportingLevelID, b.OrgLevel+1,
CAST (b.SortKey + CAST (a.LevelID AS BINARY(4))
FROM dbo.SampleOrg a
INNER JOIN SampleOrgChart b
ON a.ReportingLevelID = b.Level
WHERE b.OrgLevel < 1 )
SELECT * FROM SampleOrgChart ORDER BY SortKey
CREATE FUNCTION datesales (@deadline as datetime)
RETURNS TABLE AS RETURN ( SELECT * FROM sales WHERE ord_date > @deadline)
1st: a basic SQL query to concatenate the values of the au_fname and au_lname fields with a space.
SELECT au_fname + ' ' + au_lname AS aNameFROM authorsWHERE state = @state
2nd: In the where clause, we tell SQL Server to only return authors whose state matches the value of our @state input parameter.
WHERE state = @state
3rd: To test our new inline-table valued UDF, clear the code window in query analyzer and enter and execute the following code:
USE PUBSGOSELECT * FROM getAuthorsByState('CA')
SELECT * FROM Department D CROSS APPLY ( SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID ) A GO
SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID GO