G, You can use materialize hint in with clause to cache the result of the query.
regards, On 18 Jun 2011 03:58, "Gayathri" <gd0...@gmail.com> wrote: > > Thanks Mike.. this works...:-) > > > > On Fri, Jun 17, 2011 at 11:54 AM, Michael Moore <michaeljmo...@gmail.com> wrote: >> >> I have tested this, and I am pretty sure it works correctly according to our requirement. >> >> WITH is1234 >> AS (SELECT tab1.col2 tab1col2, >> tab1.col3 tab1col3, >> tab2.col1 tab2col1, >> tab2.col2 tab2col2, >> tab2.col3 tab2col3 >> FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2 >> WHERE tab2.col1 = 1234), >> isnot1234 >> AS (SELECT tab1.col2 tab1col2, >> tab1.col3 tab1col3, >> tab2.col1 tab2col1, >> tab2.col2 tab2col2, >> tab2.col3 tab2col3 >> FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2 >> WHERE tab2.col1 <> 1234) >> SELECT CASE >> WHEN (SELECT COUNT (*) FROM isnot1234) > 0 >> THEN >> (SELECT tab1col3 >> FROM isnot1234 o >> WHERE NOT EXISTS >> (SELECT 1 >> FROM isnot1234 i >> WHERE i.tab2col3 > o.tab2col3)) >> ELSE >> (SELECT tab1col3 >> FROM is1234 o >> WHERE NOT EXISTS >> (SELECT 1 >> FROM is1234 i >> WHERE i.tab2col3 < o.tab2col3)) >> END >> ans >> FROM DUAL; >> >> On Fri, Jun 17, 2011 at 9:31 AM, rich p <richg...@gmail.com> wrote: >>> >>> There are two analytic functions you might want to look into: first_value or last_value. It depends on how you look at the query problem at hand. You can get by using just one because if you think about it, the "FIRST_VALUE" of an ordered series sorted in DESCENDING will also be the "LAST_VALUE" of that same series when sorted in ASCENDING order. >>> >>> You can verify this using your test table and the following query sample: >>> >>> select tab1.col3, tab2.col3, >>> first_value ( tab1.col3 ) over ( order by tab2.col3 desc ) as test_1, >>> first_value ( tab1.col3 ) over ( order by tab2.col3 asc ) as test_2 >>> from tab1, tab2 >>> where tab2.col2 = tab1.col2 >>> >>> One thing you will have to consider however is that the set you order by in descending order is further constrained in your "where" clause by the value in tab2.col1 ( = 1234 or <> 1234 ) so the set you will be manipulating using with each analytic function call will be a different and mutually exclusive subset of the above query. >>> >>> Rich Pascual >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Oracle PL/SQL" group. >>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>> To unsubscribe from this group, send email to >>> oracle-plsql-unsubscr...@googlegroups.com >>> For more options, visit this group at >>> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to Oracle-PLSQL@googlegroups.com >> To unsubscribe from this group, send email to >> oracle-plsql-unsubscr...@googlegroups.com >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
