Chapter 05 - Common Table Expressions



Common Table Expressions


CTE u prezantuan me SQL Server 2005. CTE eshte nje bashkesi rreshtash e perkoheshme e emeruar.  Kjo bashkesi rreshtash mund te referohet nga nje komande SELECT, INSERT, UPDATE, ose DELETE .
Nje CTE mund te perdoret gjithashtu ne nje komande  CREATE VIEW , si pjese e komandes  SELECT qe formon VIEW.
Gjithashtu ne SQL 2008 CTE mund te perdoret me nje komandde  MERGE.

SQL Server ka dy tipe CTE—rekorsive dhe jo rekursive.


Per te krijuar nje CTE  vendoset nje klauzole WITH menjehere perpara komandes  SELECT, INSERT, UPDATE, DELETE, apo MERGE.
Klauzola  WITH mund te perfshije disa CTE.
SIntaksa e pergjithshme per krijimin e CTE eshte:

WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

...qe shpjegohet ne skicen e meposhtme...


Pasi krjohet CTE atehere ajo mund te perdoret si te ishte njew tabele e perkoheshme, jeta e se ciles zgjat vetem gjate ekzekutimit te query.

Si krijohen CTE jo rekursive

CTE jo rekursive quhet ajo CTE qe nuk referon vetveten nga brenda CTE. Shembulli me poshte krijon nje CTE me emrin empsales me dy kollona empid dhe sales.

WITH empsales(empid,sales)

AS

(

SELECT EMPLOYEEID,

COUNT(OrderId) 

from orders

group by employeeid

)

select * from empsales

Pasi eshte krijuar empsales mund te perdoret si nje tabele ne te cilen kemi ekzekuar nje SELECT.

Numri i kollonave ne klaozolen WITH percakton numrin e kollonave qe duhet te ktheje query brenda kllapave ne klauzolen AS. 

komanda me poshte

WITH empsales(empid,sales)
AS
(
SELECT EMPLOYEEID,
shipcountry,
COUNT(OrderId) 
from orders
group by employeeid,ShipCountry
)
select * from empsales

 do te jepte nje mesazh gabimi te ngjashem me:

Msg 8158, Level 16, State 1, Line 1 'empsales' has more columns than were specified in the column list.

Arsyeja per kete eshte fakti qe ne klauzolen width ne percaktojme nje CTE me dy kollona, kodin e punonjesit dhe numrin e porosive qe ai ka kryer. Ndersa ne klauzolen AS kemi nje komande SELECT qe kthen nje rezultat me tre rreshta kodin e punonjesit, emrin e shtetit dhe numrin e porosive per ate shtet nga ai punonjes. Duke qene se numri i kollonave eshte i ndryshem SQL afishon nje mesazh gabimi.


CTE mund te perdoren ne komande SELECT komplekse qe permbajne JOIN me tabela te tjera per shembull komanda:

WITH empsales(empid,sales)

AS

(

SELECT EMPLOYEEID,

COUNT(OrderId) 

from orders

group by employeeid

)

select empid,FirstName,LastName,Sales 

from empsales INNER JOIN Employees

On empsales.empid=Employees.EmployeeID


afishon kodin,emrin,mbiemrin dhe numrin e porosive te cdo punonjesi. 


Disa CTE ne nje klauzole WITH

Eshte e mundur te kemi disa CTE te ndara me presje brenda nje klauzole WITH. Shembulli i meposhtem afishon shitjet per cdo shtet ne vitin 1996 dhe 1997


Ushtrim

Te afishojme shitjet(numer porosish) per cdo shtet (ShipCountry) per sejcilin nga vitet, 1996,1997,1998 ne nje format te gatshem per te ndertuar nje grafik , si meposhte

 ShipCountry Sales1996 Sales1997 Sales1998
 Albania     10 2 20
    

Zgjidhja me poshte

with 

sales1996(Country,sales_1996)

as

(

select ShipCountry,COUNT(OrderID) 

from Orders 

where OrderDate between '1996-1-1' and '1997-1-1'

group by ShipCountry

) ,

sales1997(Country,sales_1997)

as

(

select ShipCountry,COUNT(OrderID) 

from Orders 

where OrderDate between '1997-1-1' and '1998-1-1'

group by ShipCountry

)

select sales1996.Country,sales_1996,sales_1997 

from sales1996 INNER JOIN Sales1997

on sales1996.Country=sales1997.Country 



CTE Rekursive 

CTE quhet rekursive  kur brenda komandes SELECT qe formon CTE referohet emri i CTE. 
CTE rekursive jane shume te dobishme kur punojme me te dhena hierarkike pasi CTE vazhdon te ekzekutohet deri sa query te ktheje te gjithe te dhenat.

Per te ilustruar CTE rekursive do te perdorim tabelen punonjesit. Ne kollonen ReportsTo tek atbele Employees ruajme person tek i cili punonjesi raporton. 

Nese na kerkohet te prodhojme nje strukture organizative te kompanise CTE rekursive eshte nje zgjidhje shume komode.

Megjithate duhet te jemi te kujdesshem pasi nje CTE e formuluar gabim mund te futet ne nje cikel te pafundem. Per te shmangur kete gje mund te perdorim opsionin MAXRECURSION ne komanden e jashtme SELECT, INSERT, UPDATE, DELETE, apo MERGE qe e perdor CTE.

Per me shume informacion ne lidhje me hint e query mund ti referohemi MSDN online.

http://msdn.microsoft.com/en-us/library/ms181714.aspx


Komanda me poshte afishon te dhenat e punonjesve ne menyre hierarkike duke perdorur CTE rekursive


WITH

  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

  AS

  (

    SELECT EmployeeID, FirstName, LastName, ReportsTo, 1

    FROM Employees

    WHERE ReportsTo IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo,

      r.EmpLevel + 1

    FROM Employees e

      INNER JOIN cteReports r

        ON e.ReportsTo = r.EmpID

  )

SELECT

  FirstName + ' ' + LastName AS FullName,

  EmpLevel,

  (SELECT FirstName + ' ' + LastName FROM Employees

    WHERE EmployeeID = cteReports.MgrID) AS Manager

FROM cteReports

ORDER BY EmpLevel, MgrID


Versioni 2


WITH


  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)


  AS


  (


    SELECT EmployeeID, FirstName, LastName, ReportsTo, 1


    FROM Employees


    WHERE ReportsTo IS NULL


    UNION ALL


    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo,


      r.EmpLevel + 1


    FROM Employees e


      INNER JOIN cteReports r


        ON e.ReportsTo = r.EmpID


  )



select cte.FirstName+' '+cte.LastName as fullname,

  cte.EmpLevel,

  e.FirstName+' '+e.LastName as Manager

  from cteReports cte

  left join Employees e on cte.MgrID=e.EmployeeID