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
