user3224907 user3224907 -5 years ago 237
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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download