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!

max and null values

elmasduroSep 12 2012 — edited Sep 13 2012
hi all, i have the following query
 SELECT prod,id,max(payment_date) payment_date        
        FROM balance
          WHERE run          = 25
            AND pdate   = '07-sep-2012'
            AND cd='CFACILITY'
            AND prod IS NOT NULL
            AND id IS NOT NULL
            AND payment_date IS NOT NULL
          GROUP BY prod,id
i am trying to rewrite the query so that i dont have the IS NOT NULL in the where clause. depending on the size of the table, it causes performance problem.
is there a way to rewrite the query so that i dont have to filter using IS NOT NULL but at the same time producing the correct result by leaving out the null rows.
for example, i am trying to eliminate :
AND prod IS NOT NULL
AND id IS NOT NULL
AND payment_date IS NOT NULL
but at the same time i would like to use some analytic function or other technique to filter out the data and get those rows that are not null in the select. not sure if i can filter for not null using analytic function

maybe analytic function will do. any idea?
This post has been answered by Frank Kulash on Sep 12 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 11 2012
Added on Sep 12 2012
7 comments
2,183 views