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

Analyst_SQL 3,551 Reputation points
2021-03-16T07:18:16.54+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-03-16T07:24:59.59+00:00

    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:
    78080-output.png

    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


0 additional answers

Sort by: Most helpful