Hi,
Oracle SE 12.1
Please help, Ive been pulling my hair out on this one.
I have a table; it has bank records in it.
id
account_id
tx_date
balance
I'm trying to create a view that gets the last record of each day (Im actually after the balance). Some days however will not have entries, and I want to get the last entry on the last day previous to the where clause when I query the view. However if I put the max(trunc(tx_date)) in the view, its already maxed the date and I miss some of the records.
for example, if I use:
select account_id, max(trunc(tx_date))
from bank_stmts
where tx_date <= to_date('13-MAR-2019','DD-MON-YYYY')
group by account_id
order by 1
I get the last date back for each account previous to my where condition (account_id list = 5,6,44,45)
However, If I wrap this query as though creating a view. i.e.
select * from
(
select account_id, max(trunc(tx_date)) tx_date
from bank_stmts
group by account_id
)
where tx_date <= to_date('13-MAR-2019','DD-MON-YYYY')
order by 1
With the where clause on the outside of the inner query, then it misses bank records where the tx_date is later than the where clause as the inner query is already doing the max (account list = 5,44,45)
So, how would I rewrite this so that I can create a view with the max(trunc(tx_date) on it. I need to create a view, as I need to add in some security restrictions and create a synonym on the view.
Many Thanks
Richard