Try this: (table names slightly changed, remove x)
SELECT CASE
WHEN MAX (tabx1.col3) IS NOT NULL
THEN
MAX (tabx1.col3)
ELSE
MAX ( (SELECT MAX (tabx1.col3)
FROM tabx1, tabx2
WHERE tabx2.col1 = 1234 AND tabx2.col2 = tabx1.col2))
END
col3
FROM tabx1, tabx2
WHERE tabx2.col1 <> 1234 AND tabx2.col2 = tabx1.col2 AND ROWNUM = 1
ORDER BY tabx2.col3 DESC;
On Fri, Jun 17, 2011 at 8:50 AM, Gayathri <gd0...@gmail.com> wrote:
> Hi Mike..
>
> I just added NVL(max(tab1.col3), 0) <> 0
> but how about the order by clause in the sub-query?
>
> Is there any thing I can do with analytical function?
>
> Thanks
> G
>
> On Thu, Jun 16, 2011 at 9:09 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
>> not at work, so can't check it but ...
>>
>> select case max( tab1.col3) is not null then tab1.col3 else
>> ( select tab1.col3
>> from tab1, tab2
>> where tab2.col1 = 1234
>> and tab2.col2 = tab1.col2
>> and rownum = 1
>> order by tab2.col3)
>> end col3
>> from tab1, tab2
>> where tab2.col1 <> 1234
>> and tab2.col2 = tab1.col2
>> and rownum = 1
>> order by tab2.col3 desc
>> ;
>>
>>
>> On Thu, Jun 16, 2011 at 8:47 PM, Gayathri <gd0...@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I need to combine 2 queries given below. Please suggest.
>>>
>>> Here is the details of table and its data:
>>>
>>> create table tab1
>>> (col2 number,
>>> col3 number);
>>>
>>> create table tab2
>>> (col1 number,
>>> col2 number,
>>> col3 number);
>>>
>>> insert into tab1 values(1, 233);
>>> insert into tab1 values(2, 23);
>>> insert into tab1 values(3, 23);
>>> insert into tab1 values(4, 256);
>>> insert into tab1 values(5, 130);
>>>
>>> insert into tab2 values(1234, 1, 5);
>>> insert into tab2 values(1536, 2, 3);
>>> insert into tab2 values(1536, 3, 7);
>>> insert into tab2 values(1234, 4, 5);
>>> insert into tab2 values(1234, 5, 7);
>>>
>>> ----------------------------------------------------------
>>> How can I combine these 2 query?
>>>
>>> *Query 1*
>>> select tab1.col3
>>> from tab1, tab2
>>> where tab2.col1 <> 1234
>>> and tab2.col2 = tab1.col2
>>> and rownum = 1
>>> order by tab2.col3 desc
>>> ;
>>>
>>> If no_data_found then Query 2.
>>>
>>> *Query 2*
>>> select tab1.col3
>>> from tab1, tab2
>>> where tab2.col1 = 1234
>>> and tab2.col2 = tab1.col2
>>> and rownum = 1
>>> order by tab2.col3
>>> ;
>>>
>>> If no_data_found then return NULL.
>>>
>>>
>>> Thanks
>>> G
>>>
>>> --
>>> 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
