Skip to Main Content

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!

Trying to create a view with max(trunc(date)) and use a where condition

Richard LeggeMar 19 2019 — edited Mar 19 2019

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

Comments
Post Details
Added on Mar 19 2019
8 comments
3,118 views