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
