I have the following setup, which keeps a running account balance and it is working. The problem is if you look at the code below I have a hardcoded value for the account number.
I dont want a user to enter a value. Can someone show me how it can be coverted to a (pipeline function or type?) where an account can be passed and I get the exact output as the SQL is currently generating.
Below is my test CASE. The output is generated from the last SQL in the post. Thanks in advance to all who respond and your expertise.
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 '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 'Z098555', '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 '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 '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;
/
create or replace view vwDailySummary as
SELECT d.*
,SUM (debit_total - credit_total) OVER (ORDER BY transaction_date) AS balance_to_date
FROM (SELECT account_number
,transaction_date
,SUM(DECODE(transaction_type, 'C', transaction_amount, 0)) AS credit_total
,SUM(DECODE(transaction_type, 'D', transaction_amount, 0)) AS debit_total
FROM transactions
GROUP BY account_number, transaction_date
) d
ORDER BY transaction_date;
/* transaction history for an account */
select *
from vwDailySummary
where account_number = 'Z69AG7DKS37UYU';
WITH daily_summary AS
(
SELECT
account_number,
transaction_date
, SUM (DECODE (transaction_type, 'C', transaction_amount, 0)) AS credit_total
, SUM (DECODE (transaction_type, 'D', transaction_amount , 0)) AS debit_total
FROM transactions
GROUP BY account_number, transaction_date
)
SELECT d.*
, SUM (debit_total - credit_total)
OVER (ORDER BY transaction_date) AS balance_to_date
FROM daily_summary d
WHERE account_number =
'Z69AG7DKS37UYU'
ORDER BY transaction_date;