SQL/ORACLE select unique customer_number Cust#, account_type Type, Balance Statu
ID: 3880696 • Letter: S
Question
SQL/ORACLE
select unique customer_number Cust#, account_type Type, Balance Status
from wgb_account_type natural join wgb_account
where balance > 0 AS Non-Zero Balance
union all
select unique customer_number Cust#, account_type Type, Balance Status
from wgb_account natural join wgb_transaction
where transaction_amount > 0
order by 1,2;
The following is the result of the above query statement, however, in the ststus column I want to display "Has Transaction" or "Non-zero Balance", as shown in the second table.
CUST# TYPE STATUS
------- ---------- ----------
1112401 1 11000
1112401 1 11000
1112401 2 5000
1112401 2 5000
1113004 3 2000
1113004 3 2000
1113501 2 3000
1113501 2 3000
8 rows selected.
CUST# TYPE STATUS
------- ---------- ----------
1112401 1 Has Transaction
1112401 1 Non-zero Balance
1112401 2 Has Transaction
1112401 2 Non-Zero Balance
1113004 3 Has Transaction
1113004 3 Non-Zero Balance
1113501 2 Has Transaction
1113501 2 Non-Zero Balance
8 rows selected.
Explanation / Answer
Below is the query that will display CUST#, Type and STATUS.
CASE statement is used in order to get the STATUS as ‘Non zero balance’ and ‘Has Transaction’
SELECT CUST#, TYPE,
CASE BALANCE
WHEN >0 THEN ‘Non Zero Balance’
ELSE ‘Has Balance’
END AS ‘STATUS’
FROM wgb_account_type, wgb_account;
UNION ALL
SELECT CUST#, TYPE,
CASE TRANSACTION_AMOUNT
WHEN >0 THEN ‘Has Transaction’
ELSE ‘No Transaction’
END AS ‘STATUS’
FROM wgb_account, wgb_transaction
ORDER BY 1,2;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.