I have customers. Who can have 1 or customer accounts. In each customer account there can be debits or credits in a transaction table.
I am converting a function to a MACRO in order to do performance testing. As you can see from the output below Melanie Zanzona has no transactions associated with the account and the balance is NULL. I want all NULL balances to show as zero (0). I am aware of the NVL command but I can't seem to integrate this into the SQL MACRO function and was hoping someone could help me out. The end result should be that customer Melanie Zanzona should appear last in the output as I am sorting by balance desc
Below is my test CASE.
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', 'Melanie', 'Zanzona','Y' FROM DUAL UNION ALL
SELECT 'P382319', 'Wayne', 'Zahn','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', 'Bella', 'Oxmix','Y' FROM DUAL UNION ALL
SELECT 'Z888555', 'Debra', 'Doss','Y' FROM DUAL;
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_NUMBER,
CUSTOMER_ID, VENDOR_ID,
IS_ACTIVE) AS
SELECT '0HE53T6WP9RV28P', 'Y331964', 1, 'Y' FROM DUAL
UNION ALL
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;
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
);
insert into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT '0HE53T6WP9RV28P', 'D', (LEVEL * 100.05), (DATE '2023-08-23' + 19/24) + (LEVEL-1) * (1/1440 * -5) from dual connect by level <= 15 union all
SELECT '0HE53T6WP9RV28P', 'C', (LEVEL * 50.00), (DATE '2023-08-23' + 19/24) + (LEVEL-1) * (1/1440 * -6) from dual connect by level <= 14 UNION ALL
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 varchar2
) return clob sql_macro
is
begin
return q'[
SELECT
SUM
(
CASE t.transaction_type
WHEN 'C'
THEN -t.transaction_amount
ELSE t.transaction_amount
END
)
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 ]';
end;
/
SELECT
c.customer_id,
c.first_name,
c.last_name,
(SELECT * FROM TABLE (get_customer_balance (c.customer_id))) AS balance FROM customers c
ORDER BY balance DESC;
CUSTOMER_ID FIRST_NAME
LAST_NAME BALANCE
L382059 Melanie Zanzona -
X060162 Lisa Saladino 271127.75
P382319 Wayne Zahn 203704.75
Y331964 Bella Oxmix 70609.75
Z888555 Debra Doss 48366.5
E379466 Bonnie Winterbottom 17898.8
As @JonasMetzler pointed out in the comments, you can simply use COALESCE (SQL standard) or NVL (Oracle specific) to convert the NULL value to 0 before attemping to use it for ordering.
Given your usage, it would seem preferable to have get_customer_balance return a scalar rather than a table, then you can just call it directly without a subquery:
create or replace function get_customer_balance (
i_customer_id in varchar2
) return varchar2 sql_macro(scalar)
is
begin
return q'[
SELECT
COALESCE(SUM
(
CASE t.transaction_type
WHEN 'C'
THEN -t.transaction_amount
ELSE t.transaction_amount
END
)
, 0)
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 ]';
end;
/
SELECT
c.customer_id,
c.first_name,
c.last_name,
get_customer_balance(c.customer_id) AS balance
FROM customers c
ORDER BY balance DESC;
Output:
CUSTOMER_ID FIRST_NAME LAST_NAME BALANCE
X060162 Lisa Saladino 271127.75
P382319 Wayne Zahn 203704.75
Y331964 Bella Oxmix 70609.75
Z888555 Debra Doss 48366.5
E379466 Bonnie Winterbottom 17898.8
L382059 Melanie Zanzona 0
Demo on dbfiddle.uk