I have the following setup, which is working well. As you can see I'm calling functions to get (account and customer) balances.
Is it possible to wrap these functions into CTE so I can rank (DENSE_RANK) in order to return let's say the second highest customer and account balance.
Below is my test CASE. I also included sample calls for each function that returns every row for the data provided.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE CUSTOMERS (
CUSTOMER_ID, FIRST_NAME, LAST_NAME,IS_ACTIVE) AS SELECT 'L382059', 'Leo', 'Langford','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Tom', 'Micelli','Y' FROM DUAL UNION ALL
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL UNION ALL
SELECT 'X060162', 'Lisa','Saladino','Y' FROM DUAL UNION ALL
SELECT 'Y331964', 'Sandy', 'Herring','Y' FROM DUAL UNION ALL
SELECT 'Z888555', 'Barbara', 'Broadwater','Y' FROM DUAL;
ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE vendors AS
SELECT level AS vendor_id,
'Vendor ' || level AS vendor_name
FROM dual
CONNECT BY level <= 3;
ALTER TABLE vendors
ADD ( CONSTRAINT vendors_pk
PRIMARY KEY (vendor_id));
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS
SELECT 'Z17ARWYYZRCU2Q2', 'P382319', 1, 'Y' FROM DUAL
UNION ALL
SELECT '0T81Z07CS6LXQ7Z', 'P382319', 3, 'Y' FROM DUAL
UNION ALL
SELECT 'YWYXC3Q5N9XZ7S', 'L382059', 1, 'Y' FROM DUAL UNION ALL
SELECT '612ZKAQ66VA3W3', 'Y331964', 3, 'Y' FROM DUAL UNION ALL
SELECT 'BCHD9TW78W67S1D', 'Z888555', 3, 'Y' FROM DUAL UNION ALL
SELECT '0HLS87LDR1TE8WB',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'Z69AG7DKS37UYU',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'B17ARWYYZRCU2Q2',
'X060162', 3, 'Y' FROM DUAL UNION ALL
SELECT 'THVQD6M9LR7AVK', 'E379466', 1, 'Y' FROM DUAL UNION ALL
SELECT '0Z76WT5NTLRZPTW',
'E379466', 1, 'Y' FROM DUAL;
ALTER TABLE customer_accounts
ADD CONSTRAINT customer_accounts_pk PRIMARY KEY (ACCOUNT_NUMBER);
ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_customer_fk FOREIGN KEY (CUSTOMER_ID) REFERENCES customers(customer_id);
ALTER TABLE CUSTOMER_ACCOUNTS ADD CONSTRAINT ca_vendor_fk FOREIGN KEY (VENDOR_ID) REFERENCES vendors(vendor_id);
create table transactions (
transaction_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
account_number VARCHAR2(15),
transaction_type varchar2(1) DEFAULT 'C',
transaction_amount NUMBER(10,2),
transaction_date DATE DEFAULT SYSDATE
);
ALTER TABLE TRANSACTIONS ADD CONSTRAINT transactions_account_number_fk FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES customer_accounts
(account_number);
insert into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT 'Z17ARWYYZRCU2Q2', 'D', (LEVEL * 1250.50), date '2023-05-14' + level * interval '5 15:13' day to minute from dual
connect by level <= 7
union all
SELECT 'Z17ARWYYZRCU2Q2', 'C', (LEVEL * 1175.75), date '2023-07-04' + level * interval '1 21:23' day to minute from dual
connect by level <= 5
union all
SELECT '0T81Z07CS6LXQ7Z', 'D', (LEVEL * 1250.50), date '2023-02-14' + level * interval '3 15:13' day to minute from dual
connect by level <= 17
union all
SELECT '0T81Z07CS6LXQ7Z', 'C', (LEVEL * 75.75), date '2023-02-04' + level * interval '2 21:23' day to minute from dual
connect by level <= 11
union all
select '612ZKAQ66VA3W3', 'D', 555.25 * LEVEL, (DATE '2023-07-13' + 13/24) + (level * 2) from dual
connect by level <= 25
UNION ALL
select '612ZKAQ66VA3W3', 'C', 555.25 * LEVEL, (DATE '2023-07-23' + 13/24) + (level * 2) from dual
connect by level <= 20
UNION ALL
select 'BCHD9TW78W67S1D', 'D', 1125.25 * LEVEL, date '2023-01-23' + level * interval '1 1' day to hour from dual
connect by level <= 11
union all
select 'BCHD9TW78W67S1D', 'C', 925.00 * LEVEL, date '2023-01-25' + level * interval '1 1' day to hour from dual
connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'D', (LEVEL * 1250.50), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
connect by level <= 7
union all
SELECT '0HLS87LDR1TE8WB', 'C', (LEVEL * 1175.75), date '2023-05-04' + level * interval '1 15:13' day to minute from dual
connect by level <= 5
union all
SELECT 'Z69AG7DKS37UYU', 'D', ((LEVEL * 5) * 1750), date '2023-06-01' + level * interval '1 18:43:35' day to second from dual
connect by level <= 15
union all
SELECT 'Z69AG7DKS37UYU', 'C', ((LEVEL * 5) * 1750), date '2023-06-11' + level * interval '1 15:23:49' day to second from dual
connect by level <= 13
union all
SELECT '0Z76WT5NTLRZPTW', 'D', (LEVEL * 100.57), date '2023-04-02' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT '0Z76WT5NTLRZPTW', 'C', (LEVEL * 25.26), date '2023-04-04' + level * interval '1 4' day to hour from dual
connect by level <= 5
union all
SELECT 'THVQD6M9LR7AVK', 'D', (LEVEL * 250.10), date '2023-05-10' + level * interval '1 7' day to hour from dual
connect by level <= 13
union all
SELECT 'THVQD6M9LR7AVK', 'C', (LEVEL * 133.11), (SYSDATE - LEVEL) from dual
connect by level <= 9;
CREATE OR REPLACE FUNCTION get_customer_balance
( i_customer_id IN customers.customer_id%TYPE
)
RETURN transactions.transaction_amount%TYPE
IS
v_balance transactions.transaction_amount%TYPE;
BEGIN
SELECT SUM (
CASE t.transaction_type
WHEN 'C'
THEN -t.transaction_amount
ELSE t.transaction_amount
END
)
INTO v_balance
FROM customer_accounts ca
JOIN transactions t ON t.account_number = ca.account_number
WHERE ca.customer_id = i_customer_id -- one customer
OR ca.customer_id IS NULL; -- all customers
RETURN v_balance;
END get_customer_balance;
/
CREATE OR REPLACE FUNCTION get_account_balance(
i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
SELECT SUM(
CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END
* transaction_amount
)
INTO v_balance
FROM transactions
WHERE account_number = i_account_number -- one account
OR i_account_number IS NULL; -- all accounts
RETURN v_balance;
END;
/
-- account balance works
SELECT
CA.ACCOUNT_NUMBER,
C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
get_account_balance(ca.account_number) AS balance
FROM CUSTOMER_ACCOUNTS CA
INNER JOIN customers c ON ca.customer_id = c.customer_id;
-- customer balance works
SELECT C.CUSTOMER_ID,
C.FIRST_NAME,
C.LAST_NAME,
get_customer_balance(C.CUSTOMER_ID) AS balance
FROM customers c;
You can... I'm a little unclear on whether you want to do this ranking in the function or the overall query. I'm assuming you want it in the calling query.
In which case place the queries in a CTE. In another (named) subquery use BALANCE in the ORDER BY of DENSE_RANK. Then filter the result of this in the parent query.
But honestly: you'd be better off putting the logic into your SQL statement itself. A query which calls a PL/SQL function which then calls another SQL statement has a number of issues:
- The optimizer can't merge the subquery with the parent query; this limits its ability to get optimal plans
- There are context switches between the PL/SQL and SQL engines for each execution, slowing the query down
- The query in the function starts from a later time; this can lead to incorrect results
Moving the join and sum into the query avoids all these issues. You can place the sum in the order by of dense_rank directly or calculate the sum in a subquery first, then pass this to dense_rank:
select
ca.account_number,
c.customer_id,
c.first_name,
c.last_name,
sum ( case transaction_type when 'C' then -1 else 1 end * transaction_amount ) sum_bal,
dense_rank () over (
order by sum ( case transaction_type when 'C' then -1 else 1 end * transaction_amount )
desc nulls last
) dr
from customer_accounts ca
inner join customers c on ca.customer_id = c.customer_id
left join transactions t on t.account_number = ca.account_number
group by ca.account_number, c.customer_id, c.first_name, c.last_name;
/*
ACCOUNT_NUMBER CUSTOME FIRST_N LAST_NAME SUM_BAL DR
--------------- ------- ------- ------------ ---------- ----------
Z69AG7DKS37UYU X060162 Lisa Saladino 253750 1
0T81Z07CS6LXQ7Z P382319 Tom Micelli 186327 2
612ZKAQ66VA3W3 Y331964 Sandy Herring 63853.75 3
BCHD9TW78W67S1D Z888555 Barbara Broadwater 48366.5 4
0HLS87LDR1TE8WB X060162 Lisa Saladino 17377.75 5
Z17ARWYYZRCU2Q2 P382319 Tom Micelli 17377.75 5
THVQD6M9LR7AVK E379466 Bonnie Winterbottom 16769.15 6
0Z76WT5NTLRZPTW E379466 Bonnie Winterbottom 1129.65 7
YWYXC3Q5N9XZ7S L382059 Leo Langford <null> 8
B17ARWYYZRCU2Q2 X060162 Lisa Saladino <null> 8
*/This means you're not reusing the query though. You can do this by making the function a SQL macro. As you're on 19c (assuming you're on an up-to-date patch; it was added in 19.7), this will have to be a table macro. You can only use these in the FROM/JOIN clauses, so you'll have to CROSS APPLY to it to pass the account/customer:
create or replace function get_account_balance (
i_account_number in varchar2
) return clob sql_macro
is
begin
return q'[
select sum(
case transaction_type when 'C' then -1 else 1 end
* transaction_amount
) balance
from transactions
where account_number = i_account_number
or i_account_number is null ]';
end;
/
select
ca.account_number,
c.customer_id,
c.first_name,
c.last_name,
b.balance,
dense_rank () over ( order by b.balance desc nulls last ) dr
from customer_accounts ca
inner join customers c on ca.customer_id = c.customer_id
cross apply ( select * from get_account_balance(ca.account_number) ) b;
/*
ACCOUNT_NUMBER CUSTOME FIRST_N LAST_NAME BALANCE DR
--------------- ------- ------- ------------ ---------- ----------
Z69AG7DKS37UYU X060162 Lisa Saladino 253750 1
0T81Z07CS6LXQ7Z P382319 Tom Micelli 186327 2
612ZKAQ66VA3W3 Y331964 Sandy Herring 63853.75 3
BCHD9TW78W67S1D Z888555 Barbara Broadwater 48366.5 4
Z17ARWYYZRCU2Q2 P382319 Tom Micelli 17377.75 5
0HLS87LDR1TE8WB X060162 Lisa Saladino 17377.75 5
THVQD6M9LR7AVK E379466 Bonnie Winterbottom 16769.15 6
0Z76WT5NTLRZPTW E379466 Bonnie Winterbottom 1129.65 7
B17ARWYYZRCU2Q2 X060162 Lisa Saladino <null> 8
YWYXC3Q5N9XZ7S L382059 Leo Langford <null> 8
*/Or add customer/account to the query results in the macro with appropriate grouping and join it like a regular table.