I prefer TABLE Functions over ref cursors, but I guess it's a matter of personal choice.
Regards, Mike On Mon, Jun 20, 2011 at 8:39 AM, Jignesh Makwana <makwanajigne...@gmail.com>wrote: > 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 > -- 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
