Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2210

Re: Custom window functions

Show all headers | View raw

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Does MSSQL support custom window functions?  For example,
> suppose I want to implement exponential smoothing in .NET
> and register it as a window function in MSSQL -- is that
> possible?
> 

Depends on what you want to achieve. This works:

   SELECT object_id, name, 
          dbo.integerlist(column_id) OVER(PARTITION BY object_id)
   FROM   sys.columns
   ORDER BY object_id, column_id

dbo.integerlist is a user-defined aggegrate implemented in C#.

But when I tried:

   SELECT object_id, name, 
          dbo.integerlist(column_id) OVER(PARTITION BY object_id 
             ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
   FROM   sys.columns
   ORDER BY object_id, column_id

I got 

   Msg 156, Level 15, State 1, Line 3
   Incorrect syntax near the keyword 'ORDER'.

The error is a little surprising. But presumably there are different parse-
trees for built-in and user-defined functions. And supposedly when they
introduced windowed aggregates in SQL 2012, they only updated the parse
tree for tbe built-ins. Which opens the question what would happen if
they fixed the parser...

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-22 12:32 +0300
  Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-23 20:56 +0200
    Re: Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-25 18:38 +0300
      Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-25 21:27 +0200
        Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-26 21:00 +0200
          Re: Custom window functions Anton Shepelev <anton.txt@gmail.moc> - 2023-05-26 22:55 +0300

csiph-web