13 Replies Latest reply on Sep 5, 2017 4:28 AM by John Hobby

    Question about custom SQL using LEAD

    Shay Levy

      Hello,

       

      I have a custom SQL query and I would like to use to LEAD function in it to get data from a next row. I keep getting the following error:

       

      Database error 0x80040E14: Syntax error (missing operator) in query expression '(LEAD(['Base Data$'].[Date],1,NULL) OVER (PARTITION BY ['Base Data$'].[Transaction ID]))'.

       

      I know that the LEAD line should also be added to the GROUP BY clause. For now i'm just trying to understand why I keep getting a syntax error.

      Here is my query:

       

      SELECT

      ['Base Data$'].[Transaction ID] AS [Transaction ID],

      ['Base Data$'].[Date] AS [Date],

      IIF(['Base Data$'].[Interaction] = "Opportunity","Proposed") AS OppStatus,

      IIF(['Base Data$'].[Interaction] = "Opportunity",['Base Data$'].[Date]) AS OppDate,

      IIF(['Base Data$'].[Interaction] = "Opportunity",['Base Data$'].[Transaction Amount]) AS OppAmt,

      IIF(['Base Data$'].[Interaction] = "New Client Project","Accepted") AS EngStatus,

      IIF(['Base Data$'].[Interaction] = "New Client Project",['Base Data$'].[Date]) AS EngDate,

      IIF(['Base Data$'].[Interaction] = "New Client Project",['Base Data$'].[Transaction Amount]) AS EngAmt,

      (LEAD(['Base Data$'].[Date],1,NULL) OVER (PARTITION BY ['Base Data$'].[Transaction ID])) AS Closed_Date

      FROM ['Base Data$']

      GROUP BY ['Base Data$'].[Transaction ID],

      ['Base Data$'].[Date],

      IIF(['Base Data$'].[Interaction] = "Opportunity","Proposed"),

      IIF(['Base Data$'].[Interaction] = "Opportunity",['Base Data$'].[Date]),

      IIF(['Base Data$'].[Interaction] = "Opportunity",['Base Data$'].[Transaction Amount]),

      IIF(['Base Data$'].[Interaction] = "New Client Project","Accepted"),

      IIF(['Base Data$'].[Interaction] = "New Client Project",['Base Data$'].[Date]),

      IIF(['Base Data$'].[Interaction] = "New Client Project",['Base Data$'].[Transaction Amount])

       

      Thanks,

      Shay