'lead' is not a recognized built-in function name.

Question

I am executing query in sql 2014 ,it is working fine ,but i am executing same query in 2008 it giving error

Msg 195, Level 15, State 10, Line 22
'lead' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 40
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 61
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 73
Incorrect syntax near ','.

Below is query

 ;with I as
 (
     select *
     from Dispatch_BD
 where DID in (4013)
 ),
 N as
 (
     select *, row_number() over (partition by DID,codeitem order by ID) - 1 as n
     from I
 ),
 P as
 (
     select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r
     from N
 ) ,
 G as
 (
     select *,
         isnull(lead(BweightT, 0) over (partition by DID,codeitem order by ID), '') as [1],
         isnull(lead(BweightT, 1) over (partition by DID,codeitem order by ID), '') as [2],
         isnull(lead(BweightT, 2) over (partition by DID,codeitem order by ID), '') as [3],
         isnull(lead(BweightT, 3) over (partition by DID,codeitem order by ID), '') as [4],
         isnull(lead(BweightT, 4) over (partition by DID,codeitem order by ID), '') as [5],
         isnull(lead(BweightT, 5) over (partition by DID,codeitem order by ID), '') as [6],
         isnull(lead(BweightT, 6) over (partition by DID,codeitem order by ID), '') as [7],
         isnull(lead(BweightT, 7) over (partition by DID,codeitem order by ID), '') as [8],
         isnull(lead(BweightT, 8) over (partition by DID,codeitem order by ID), '') as [9],
         isnull(lead(BweightT, 9) over (partition by DID,codeitem order by ID), '') as [10]
     from P
 ) ,
 R as
 (
     select *
     from G
     where c = 1
 )
 ,
 Max as
 (
     select codeitem,max(r) max
     from G
     where c = 1
     group by codeitem
 ),
 F as
 (
     select
         case r when 1 then t.Descriptionitem else '' end as Item,
     case r when 1 then cast((select count(*) from I where codeitem = R.codeitem and did=r.did) as varchar(max)) else '' end as DQTY,
     case when r=max then
         cast((select sum(Bweight) from I where codeitem = R.codeitem) as varchar(max))
         else '' end
         as Total,
         R.*
     from R
     inner join ItemMasterFile t on t.codeitem = R.codeitem
     left join max m on m.codeitem=r.codeitem    
 ),
 GT as
 (
     select cast(count(*) as varchar(max)) as GrandTotalQTY,
         cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight
     from I    
 )
 ,
 E as
 (
     select distinct DID
     from I
 ),
 U as
 (
     select DID, Item, DQTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2
     from F
     union all
     select DID, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1
     from E
     union all
     select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2
     from GT
 )
 select Item, DQTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total
 from U
 order by ord2, DID, ord1

Answer 1

Hi @Analyst_SQL ,

LEAD is supported starting with SQL Server 2012 (11.x).

So you will receive "'lead' is not a recognized built-in function name." error if you use this function in SQL 2008.

Please refer below:

DROP TABLE IF EXISTS #item,#DistpatchM,#Distpatch_BD  
       
 CREATE TABLE #item (ItemCode int,ItemName varchar(max))      
 CREATE TABLE #DistpatchM(DID int,E_Date date)  
 CREATE TABLE #Distpatch_BD(ID int, Itemcode int, DID int, DisQTY int, Bweight int)  
          
 INSERT INTO #item VALUES(1001, 'A')  
 INSERT INTO #item VALUES(1002, 'B')  
 INSERT INTO #item VALUES(1003, 'C')  
 INSERT INTO #item VALUES(1004, 'D')  
 INSERT INTO #item VALUES(1005, 'E')  
 INSERT INTO #item VALUES(1006, 'F')  
 INSERT INTO #item VALUES(1007, 'G')  
 INSERT INTO #DistpatchM values(111,'2019-05-28')  
 INSERT INTO #DistpatchM values(112,'2018-05-29')                 
 INSERT INTO #Distpatch_BD values(1,1001,111,1 ,500)  
 INSERT INTO #Distpatch_BD values(2,1002,111 ,1,600)  
 INSERT INTO #Distpatch_BD values(3,1003,111 ,1,800)  
 INSERT INTO #Distpatch_BD values(4,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(5,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(6,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(7,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(8,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(9,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(10,1006,112,1,900)  
 INSERT INTO #Distpatch_BD values(11,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(12,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(13,1006,112,1,4500)  
 INSERT INTO #Distpatch_BD values(14,1006,112,1,650)  
 INSERT INTO #Distpatch_BD values(14,1001,112,1,400)  
      
;with I as  
 (  
     select *  
     from #Distpatch_BD   
 where DID in (112)  
 ),  
 N as  
 (  
     select *, row_number() over (partition by DID,ITEMCODE order by ID) - 1 as n  
     from I  
 ),  
 P as  
 (  
     select *, cast(Bweight as varchar(max)) as BweightT, n % 10 + 1 as c, n / 10 + 1 as r  
     from N  
 )  ,  
 G as  
 (  
 select p.*,  
 ISNULL(P0.BweightT,'') [1],  
 ISNULL(P1.BweightT,'') [2],  
 ISNULL(p2.BweightT,'') [3],  
 ISNULL(P3.BweightT,'') [4],  
 ISNULL(P4.BweightT,'') [5],  
 ISNULL(P5.BweightT,'') [6],  
 ISNULL(P6.BweightT,'') [7],  
 ISNULL(P7.BweightT,'') [8],  
 ISNULL(P8.BweightT,'') [9],  
 ISNULL(P9.BweightT,'') [10]  
 from p   
 left join p p0   
 on p.did=p0.did and p.itemcode=p0.itemcode and p.n=p0.n  
 left join p p1   
 on p.did=p1.did and p.itemcode=p1.itemcode and p.n=p1.n-1   
 left join p p2   
 on p.did=p2.did and p.itemcode=p2.itemcode and p.n=p2.n-2  
 left join p p3   
 on p.did=p3.did and p.itemcode=p3.itemcode and p.n=p3.n-3  
 left join p p4  
 on p.did=p4.did and p.itemcode=p4.itemcode and p.n=p4.n-4  
 left join p p5  
 on p.did=p5.did and p.itemcode=p5.itemcode and p.n=p5.n-5  
 left join p p6  
 on p.did=p6.did and p.itemcode=p6.itemcode and p.n=p6.n-6  
 left join p p7  
 on p.did=p7.did and p.itemcode=p7.itemcode and p.n=p7.n-7  
 left join p p8  
 on p.did=p8.did and p.itemcode=p8.itemcode and p.n=p8.n-8  
 left join p p9  
 on p.did=p9.did and p.itemcode=p9.itemcode and p.n=p9.n-9  
)   
 ,  
 R as  
 (  
     select *   
     from G   
     where c = 1  
 )  
 ,  
 Max as  
 (  
     select ITEMCODE,max(r) max   
     from G   
     where c = 1  
     group by ITEMCODE  
 ),  
 F as  
 (  
     select   
         case r when 1 then t.ItemName else '' end as Item,   
     case r when 1 then cast((select count(*) from I where ITEMCODE = R.ITEMCODE and did=r.did) as varchar(max)) else '' end as QTY,  
     case when r=max then  
         cast((select sum(Bweight) from I where ITEMCODE = R.ITEMCODE) as varchar(max))   
         else '' end   
         as Total,  
         R.*  
     from R  
     inner join #Item t on t.ITEMCODE = R.ITEMCODE  
     left join max m on m.ITEMCODE=r.ITEMCODE       
 ),  
 GT as  
 (  
     select cast(count(*) as varchar(max)) as GrandTotalQTY,   
         cast(sum(I.Bweight) as varchar(max)) as GrandTotalBWeight  
     from I       
 )  
 ,  
 E as  
 (  
     select distinct DID  
     from I  
 ),  
 U as  
 (  
     select DID, Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total, 1 as ord1, 1 as ord2  
     from F  
     union all  
     select DID, '', '', '', '', '', '', '', '', '', '', '', '', '', 2, 1  
     from E  
     union all  
     select NULL, 'Grand Total', GrandTotalQTY, '', '', '', '', '', '', '', '', '', '', GrandTotalBWeight, 1, 2  
     from GT  
 )  
 select Item, QTY, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], Total  
 from U  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.

1: https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15 [3]: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html