Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get customer balances (indircect JOIN)

PugzlyJun 23 2023

I have 3 tables: customers -> customer_accounts -> transactions.

My goal is to get each customer's balance similar to the way I get each account balance by calling the function get_account_balance if possible.

Since I don't currently save the customer_id in the transactions table is there a way to indirectly get this value or do I need to add customer_id to the transactions table, which would probably solve my problem.

In summary, I am trying to get an output like this:

CUSTOMER_ID FIRST_NAME LAST_NAME IS_ACTIVE BALANCE

D379171 John Carucci Y 84853.75

E379466 Bonnie Winterbottom Y 17500

K380989 Lisa Saladino Y 46566.5

…

…

…

Below is my setup and test CASE. Any help would be greatly appreciated.

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 'A355135', 'Otto', 'Vatsch','Y' FROM DUAL  UNION ALL 
SELECT 'B375480', 'Sophia', 'Fazio','Y' FROM DUAL  UNION ALL 
SELECT 'C378853', 'Brian', 'Vendome','Y' FROM DUAL  UNION ALL 
SELECT 'D379171', 'John', 'Carucci','Y' FROM DUAL  UNION ALL 
SELECT 'E379466', 'Bonnie', 'Winterbottom','Y' FROM DUAL  UNION ALL 
SELECT 'F379739', 'Debra', 'Caygle','Y' FROM DUAL  UNION ALL 
SELECT 'G379994', 'Jerry', 'Torciano','Y' FROM DUAL  UNION ALL
SELECT 'H380235', 'Karl', 'Malden','Y' FROM DUAL  UNION ALL
SELECT 'I380492', 'Joseph', 'Zaza','Y' FROM DUAL  UNION ALL
SELECT 'J380753', 'Tom', 'Micelli','Y' FROM DUAL  UNION ALL
SELECT 'K380989', 'Lisa', 'Saladino','Y' FROM DUAL  UNION ALL
SELECT 'L381307', 'Jeff', 'Lebowitz','Y' FROM DUAL  UNION ALL
SELECT 'M381569', 'Brian', 'Zanona','Y' FROM DUAL  UNION ALL
SELECT 'N381823', 'Seth', 'Bobet','Y' FROM DUAL  UNION ALL
SELECT 'O382059', 'Mitch', 'Weinreb','Y' FROM DUAL  UNION ALL
SELECT 'P382319', 'Roz', 'Stern','Y' FROM DUAL  UNION ALL
SELECT 'Q382564', 'Zoey', 'Zanzone','Y' FROM DUAL  UNION ALL
SELECT 'R382815', 'Charles', 'Stein','Y' FROM DUAL  UNION ALL
SELECT 'S441015', 'Tony', 'Dimeo','Y' FROM DUAL  UNION ALL
SELECT 'T441333', 'Faith', 'Carrucci','Y' FROM DUAL  UNION ALL
SELECT 'X098533', 'Brian', 'Tessio','Y' FROM DUAL  UNION ALL
SELECT 'Y098273', 'Cheryl', 'Brasi','Y' FROM DUAL  UNION ALL
SELECT 'Z098555', 'Peter', 'Clemenza','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 <= 10;


 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 'B17ARWYYZRCU2Q2', 
'D379171', 8, 'Y' FROM DUAL  UNION ALL 
SELECT '0T81Z07CS6LXQ7Z',
'D379171', 7, 'Y' FROM DUAL  UNION ALL 
SELECT 'YWYXC3Q5N9XZ7S',
'D379171', 7, 'Y' FROM DUAL  UNION ALL 
SELECT '612ZKAQ66VA3W3',
'D379171', 4, 'Y' FROM DUAL  UNION ALL 
SELECT 'THVQD6M9LR7AVK', 'E379466', 5, 'Y' FROM DUAL  UNION ALL 
SELECT 'CFM9K06ZR98R9H2', 'K380989', 1, 'Y' FROM DUAL  UNION ALL 
SELECT '0Z8NQN6JZRZWFPX',
'K380989',  2, 'Y' FROM DUAL  UNION ALL 
SELECT 'BCHD9TW78W67S1D',
 'K380989', 3, 'Y' FROM DUAL  UNION ALL 
SELECT '01SUV1Y3BJTCFXY',
'X098533', 1, 'Y' FROM DUAL  UNION ALL 
SELECT 'TAJ3N5EB9ZX7AD',
'X098533', 2, 'Y' FROM DUAL  UNION ALL 
SELECT 'Y88JTBCP8SUFY8',
'X098533', 3, 'Y' FROM DUAL  UNION ALL 
SELECT '06LP3CYJLS01F2L',
'Y098273', 7, 'Y' FROM DUAL  UNION ALL 
SELECT 'TFWVBRC5QHQLC4', 'Y098273', 8, 'Y' FROM DUAL  UNION ALL 
SELECT '0Z76WT5NTLRZPTW', 'Z098555', 10, 'Y' FROM DUAL;


ALTER TABLE customer_accounts 
ADD CONSTRAINT customer_accounts_pk PRIMARY KEY (ACCOUNT_NUMBER);


ALTER TABLE customer_accounts ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);


ALTER TABLE customer_accounts ADD CONSTRAINT vendors_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),
    transaction_amount NUMBER(10,2),
     transaction_date DATE
);


insert  into transactions(
account_number, transaction_type, transaction_amount, transaction_date)
SELECT 'B17ARWYYZRCU2Q2', 'D', (LEVEL * 1000), DATE'2022-10-22'
    + interval '8' hour + interval '10' minute + interval '49' second + ((level -1) * interval '2' hour + level * interval '10' minute)
     FROM dual connect by level <= 5 UNION ALL 
SELECT 'YWYXC3Q5N9XZ7S',
 'D', 1000, to_date('17-Oct-2022 08:00', 'DD-MM-YYYY HH:MI')  + interval '2:10' hour to minute + (level * interval '4:17' hour to minute) FROM dual connect by level <= 15 UNION ALL 
SELECT 'YWYXC3Q5N9XZ7S',
 'C', 1000, to_date('20-Oct-2022 08:00', 'DD-MM-YYYY HH:MI')  + interval '5:55' hour to minute + (level * interval '4:47' hour to minute) FROM dual 
    connect by level <= 9  UNION ALL 
select '612ZKAQ66VA3W3', 'D', 555.25 * LEVEL, (DATE '2023-04-01' + 13/24) + (level * 2)  from dual 
    connect by level <= 25 union all
select '612ZKAQ66VA3W3', 'C', 555.25 * LEVEL, (DATE '2023-04-03' + 13/24) + (level * 2)  from dual 
    connect by level <= 20 UNION ALL 
select 'BCHD9TW78W67S1D', 'D', 125.25 * LEVEL, date '2023-04-01' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 'BCHD9TW78W67S1D', 'C', 25.00 * LEVEL, date '2023-04-02' + level * interval '1 1' day to hour from dual
          connect by level <= 7
union all
SELECT '0Z8NQN6JZRZWFPX', 'D', 1000 * LEVEL, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
SELECT '0Z8NQN6JZRZWFPX', 'C', 500 * LEVEL, date '2023-03-30' + level * interval '2' day from dual
          connect by level <= 12
union all
SELECT 'TAJ3N5EB9ZX7AD', 'D', 1500.00 * LEVEL, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
SELECT 'TAJ3N5EB9ZX7AD', 'C', 750.00 * LEVEL, date '2023-01-15' + level * interval '11' hour from dual
          connect by level <= 13
union all
SELECT 'Y88JTBCP8SUFY8', 'D', 575.50 * LEVEL, date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 15
union all
SELECT 'Y88JTBCP8SUFY8', 'C', 500.00 * LEVEL, date '2023-04-13' + level * interval '1 3' day to hour from dual
          connect by level <= 15
union all
SELECT '06LP3CYJLS01F2L', 'D', 500,  date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 20
union all
SELECT '06LP3CYJLS01F2L', 'C', 500,  date '2023-02-11' + level * interval '2 10:15' day to minute from dual
          connect by level <= 10
union all
SELECT 'TFWVBRC5QHQLC4', 'D', 250 *LEVEL, date '2023-04-22' + level * interval '20' hour from dual
          connect by level <= 23
union all
SELECT 'TFWVBRC5QHQLC4', 'C', 250 *LEVEL, date '2023-05-10' + level * interval '23' hour from dual
          connect by level <= 14
union all
SELECT '0Z76WT5NTLRZPTW', 'D', 1500, date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
SELECT '0Z76WT5NTLRZPTW', 'D', 1500, date '2022-03-01' + level * interval '1 00:23:35' day to second from dual
          connect by level <= 15
union all
SELECT 'THVQD6M9LR7AVK', 'D', 500, date '2023-05-01' + level * interval '5' hour from dual
          connect by level <= 60
union all
SELECT 'THVQD6M9LR7AVK', 'C', 500, date '2023-05-10' + level * interval '9' hour from dual
          connect by level <= 25;


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;
/


SELECT CA.ACCOUNT_NUMBER,
       C.FIRST_NAME,
       C.LAST_NAME, 
       CA.IS_ACTIVE,
       get_account_balance(ca.account_number) AS balance
FROM   CUSTOMER_ACCOUNTS CA
       INNER JOIN customers c
       ON ca.customer_id = c.customer_id;


ACCOUNT_NUMBER FIRST_NAME LAST_NAME IS_ACTIVE   BALANCE
B17ARWYYZRCU2Q2 John Carucci Y 15000
0T81Z07CS6LXQ7Z John Carucci Y - 
YWYXC3Q5N9XZ7S John Carucci Y 6000
612ZKAQ66VA3W3 John Carucci Y 63853.75
THVQD6M9LR7AVK Bonnie Winterbottom Y 17500
CFM9K06ZR98R9H2 Lisa Saladino Y - 
0Z8NQN6JZRZWFPX Lisa Saladino Y 39000
BCHD9TW78W67S1D Lisa Saladino Y 7566.5
01SUV1Y3BJTCFXY Brian Tessio Y - 
TAJ3N5EB9ZX7AD Brian Tessio Y 111750
Y88JTBCP8SUFY8 Brian Tessio Y 9060
06LP3CYJLS01F2L Cheryl Brasi Y 5000
TFWVBRC5QHQLC4 Cheryl Brasi Y 42750
0Z76WT5NTLRZPTW Peter Clemenza Y 45000
This post has been answered by Solomon Yakobson on Jun 23 2023
Jump to Answer
Comments
Post Details
Added on Jun 23 2023
#sql
2 comments
504 views
1 mention