user3224907 user3224907 - 8 months ago 55
SQL Question

ORA-01799 - need to correct query

Not sure how to rewrite the below query. I’m trying to join table_a to the most recent table_b record. Currently testing for only one ID, but a different criteria on table_a may be added:

Select t.*
from table_a t
left join table_b d on = and d.MOD_DATE IN (SELECT MAX(mod_date) FROM table_b d2 WHERE =
where = 123456

Any suggestions?


I think you are looking for something like:

SELECT      t.*
FROM        table_a t
                SELECT      d.*
                FROM        table_b d 
                INNER JOIN  (
                                SELECT  id
                                ,       MAX(mod_date) mod_date_max
                                FROM    table_b d2 
                                GROUP BY id
                            ) db
                        ON =
                        AND db.mod_date_max = d.mod_date
            ) d
        ON = 
WHERE = 123456

Note that your where clause turns the left join into an inner join.

Also, if you get an error, please post the error message as well, not just its number.