100% Agree, We can write a function to return ref cursor.
That would be the best solution. Regards, Jignesh. On 20 Jun 2011 21:01, "Michael Moore" <michaeljmo...@gmail.com> wrote: > > Hi Jignesh, > The root of the problem is that the final query is really dependent on some aspect of the data itself. > > If DATA says X > then perform QUERY A > else > perform QUERY B > > I question the need to combine all of this into a single query. What I would do is use a PL/SQL block unless there was some overriding concern that dictated a single query. > > Mike > > > On Sun, Jun 19, 2011 at 9:43 PM, Jignesh Makwana < makwanajigne...@gmail.com> wrote: >> >> We would like to see the efficient way Mike. >> At least, what I believe is first solve the problem, then look for efficiency. >> Now, as problem has been solved, We can look for efficiency. >> >> Regards, >> >> >> On Sat, Jun 18, 2011 at 8:09 PM, Michael Moore <michaeljmo...@gmail.com> wrote: >>> >>> It's probably not the most efficient way to go about it and if it was a large table, I'd probably do it a different way. The one advantage it might have is for maintenance. I.E, the logic of what you are trying to do is clearly exposed by the structure of the statement. >>> >>> Mike >>> >>> >>> >>> On Fri, Jun 17, 2011 at 8:47 PM, Jignesh Makwana < makwanajigne...@gmail.com> wrote: >>>> >>>> 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 >>> >>> >>> -- >>> 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
