moonir01 moonir01 - 4 months ago 10
SQL Question

create view (subtract two different columns on different tables)

Can I create another view? subtract from total_deposit - total_loan_amount

CREATE VIEW v_total_deposit (total_deposit) as
select sum(deposit_amount)
from deposit;

CREATE VIEW v_total_loan (total_loan) as
SELECT SUM(loan_amount)
from loan;

Answer

@moonir01 MINUS is a set operator, not arithmetic one. You have to subtract two numbers:

CREATE VIEW balance
AS
   SELECT NVL((SELECT TOTAL_DEPOSIT FROM V_TOTAL_DEPOSIT),0) - NVL((SELECT TOTAL_LOAN FROM V_TOTAL_LOAN),0) AS balance
     FROM DUAL 

but you really don't need these views.