43 lines
1.2 KiB
SQL
43 lines
1.2 KiB
SQL
|
|
create or replace view rcnt.transaction_history_view as
|
|
select
|
|
t.trns_id,
|
|
t.trns_amount,
|
|
t.trns_description,
|
|
b.bkt_id,
|
|
b.bkt_dsply_code,
|
|
b.bkt_dsply_name,
|
|
a.acnt_id,
|
|
a.acnt_dsply_name
|
|
from rcnt.transactions t
|
|
left join rcnt.buckets b on b.bkt_id = t.trns_bucket
|
|
left join rcnt.accounts a on a.acnt_id = t.trns_account
|
|
|
|
drop view rcnt.transaction_history_view;
|
|
drop view rcnt.transaction_detail_history_view;
|
|
|
|
select * from rcnt.transaction_history_view
|
|
|
|
create or replace view rcnt.transaction_detail_history_view as
|
|
select
|
|
thv.trns_id,
|
|
thv.trns_amount,
|
|
thv.acnt_dsply_name,
|
|
t.trns_brkdwn_amount as breakdown_amount,
|
|
b.bkt_dsply_code,
|
|
b.bkt_dsply_name,
|
|
tc.trns_ctgry_dsply_code,
|
|
tc.trns_ctgry_dsply_name
|
|
from rcnt.transaction_history_view thv
|
|
left join rcnt.transaction_breakdown t on thv.trns_id = t.trns_brkdwn_parent_transaction
|
|
left join rcnt.transaction_categories tc on tc.trns_ctgry_id = t.trns_brkdwn_catagory
|
|
left join rcnt.buckets b on b.bkt_id = t.trns_brkdwn_bucket
|
|
|
|
grant connect on database "Borealis" to rcntuser;
|
|
grant pg_read_all_data to rcntuser;
|
|
grant pg_write_all_data to rcntuser;
|
|
|
|
SELECT trns_id, trns_amount, trns_description, trns_account, trns_bucket, trns_date
|
|
FROM rcnt.transactions;
|
|
|
|
|