How to Call a User Defined Function to Use with Select, Group By, Order By

How do I call a User Defined Function to use with select, group by, order by?

You can join to your table like a view and have your function call there. That way you can call the group by and order by on the column from the view.

select
    Count(Page) as VisitingCount,
    [Time]
from
(
    SELECT
        Page,
        Date,
        [user],
        dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108)) as [Time]
    FROM
        scr_SecuristLog
) scr_SecuristLog2
where
    Date between '2009-04-30' and '2009-05-02'
and
    [user] in
(
    select
        USERNAME
    from
     scr_CustomerAuthorities
    where
        customerID=Convert(varchar,4)
    and
        ID=Convert(varchar,43)
)
group by
    [Time]
order by
    [Time] asc 

SQL Group By On Output From User Defined Function

You should be able to group by the functions themselves, not by the aliases

select id
      ,extract_data(data,abc) as abc
      ,extract_data(data,def) as def
      ,count(*)
from   table
group by id
        ,extract_data(data,abc)
        ,extract_data(data,def) 

Note that this does not generally involve executing the function multiple times. You can see that yourself with a simple function that increments a counter in a package every time it is called

SQL> ed
Wrote file afiedt.buf

  1  create or replace package pkg_counter
  2  as
  3    g_cnt integer := 0;
  4* end;
SQL> /

Package created.

SQL> create or replace function f1( p_arg in number )
  2    return number
  3  is
  4  begin
  5    pkg_counter.g_cnt := pkg_counter.g_cnt + 1;
  6    return mod( p_arg, 2 );
  7  end;
  8  /

Function created.

There are 16 rows in the EMP table

SQL> select count(*) from emp;

  COUNT(*)
----------
        16

so when we execute a query that involves grouping by the function call, we hope to see the function executed only 16 times. And that is, in fact, what we see.

SQL> select deptno,
  2         f1( empno ),
  3         count(*)
  4    from emp
  5   group by deptno,
  6            f1( empno );

    DEPTNO  F1(EMPNO)   COUNT(*)
---------- ---------- ----------
                    1          1
        30          0          4
        20          1          1
        10          0          2
        30          1          2
        20          0          4
        10          1          1
                    0          1

8 rows selected.

SQL> begin
  2    dbms_output.put_line( pkg_counter.g_cnt );
  3  end;
  4  /
16

PL/SQL procedure successfully completed.

Create a user defined function that works with GROUP BY in mysql

User defined aggregate stored functions were added in MariaDB-10.3.3

MySQL can do aggregate functions however not in SQL. They need a UDF (shared library implemenation)

Calling a user defined scalar function from a sql program

Well, the function should read like this at least if it's for SQL Server: what you have above is wrong

ALTER FUNCTION dbo.FN_LTV_Ranges
(

   @LTV_RANGE     decimal(4,3)
)
Returns varchar(16)
as 
Begin
declare @Return     varchar(16)
select  @Return =
        Case 
        When @LTV_Range is NULL then 'Missing'
        When @LTV_Range = 0 then 'Missing'
        When @LTV_Range <= 0.75 then '<=0.75'
        When @LTV_Range between 0.75 and 0.80 then '75-80'
        When @LTV_Range between 0.80 and 0.90 then '80-90'
        When @LTV_Range between 0.90 and 1.00 then '90-100'
        When @LTV_Range >= 100 then '100+'
        else null end
        Return @Return
END

For decimal(4,3) your min/max is +/- 9.999 so why this "When @LTV_Range >= 100 then '100+'"?

Next, why have OPENQUERY submitting a SQL call to a DB2 instance that includes a SQL Server function?

I assume you want the function call + grouping + ordering outside. And where do you set @LTV_Range?

Finally, grouping + ordering on @LTV_Range is pointless: it's a single value so I assume you mean to group/order on the result of the function call

declare @LTV_Range decimal(4,3)
Select top 600
   s.LNumber, dbo.FN_LTV_Range(@LTV_Range)
from
     OPENQUERY (SvrLink,  '
Select Lnumber
 from some_table s
        where s.LNumber > '0'
for Fetch only with UR')
group by dbo.FN_LTV_Range(@LTV_Range)
Order by dbo.FN_LTV_Range(@LTV_Range)

The question as it stands makes no sense I'm sorry to say...

jOOQ: How to call Sql User defined Function Within Select Query

User-defined functions are generated in a Routines class. You can just static-import all methods from that class:

import static com.example.generated.Routines.*;

And then, writing f_feeAmount(arg) should be fine.

See also this page of the jOOQ manual about generated global artefacts.

SQL Group By with an Order By

In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:

SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20

Using a custom aggregate function in a GROUP BY?

EDIT: I can confirm this works very very well on a large database (30,000 values)

Hmm... Just came across this so the following works perfectly fine but am not sure how expensive it can turn out to be:

SELECT
   GroupName,
   AVG(Value)
FROM
(
   SELECT
      GroupName,
      cast(Value as decimal(5,2)) Value,
      ROW_NUMBER() OVER (
         PARTITION BY GroupName
         ORDER BY Value ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY GroupName 
         ORDER BY Value DESC) AS RowDesc
   FROM #TEMP SOH
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY GroupName
ORDER BY GroupName;

---

Related Topics