RE: SELECT statement issue

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Fri, 15 Sep 2006 11:09:01 -0400
Message-ID: <D6424CD4C8A3C044BBC49877ED51C51801DBB0BB@ex2003.metratech.com>

Ganesh,  

Thanks a lot, when I am trying to test this I can see the following behavior:

SQL> select decode(id_acc,123,(select count(*) from tab1),124,(select count

(*) from tab1_mapper)) from tab1 where id_acc=123;  

DECODE(ID_ACC,123,(SELECTCOUNT

---

                            64

 

SQL> select decode(id_acc,123,(select * from tab1),124,(select count(*) fro

m tab1_mapper)) from tab1 where id_acc=123;

select decode(id_acc,123,(select * from tab1),124,(select * from tab1_mapper)) from tab1 where id_acc=123

                                 *

ERROR at line 1:

ORA-00913: too many values    

SQL> select decode(id_acc,123,(select id_acc from tab1),124,(select count(*

) from tab1_mapper)) from tab1 where id_acc=123;

select decode(id_acc,123,(select id_acc from tab1),124,(select id_acc from tab1_mapper)) from tab1 where id_acc=123

                                 *

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row  

It looks like it allows count(*) type of return from select but out actual code is returing rows of data and oracle is throwing : too many values.  

Let me know if there is workaround.  

Thanks

--Harvinder
   

---

From: Ganesh Raja [mailto:ganesh.raja_at_gmail.com] Sent: Thursday, September 14, 2006 4:26 PM To: Harvinder Singh
Cc: ORACLE-L
Subject: Re: SELECT statement issue  

Select Decode (values, 'XYZ', (Select ....), 'ABC', (Select .....), (Select ...))

          <<IF>     <Equalto>                       <elseif>

<else>  

HTH  

• Ganesh

On 9/14/06, Harvinder Singh <Harvinder.Singh_at_metratech.com> wrote:

Hi,  

We are porting the application from SQL Server to Oracle, we have lot of queries in the xml file and the application read the queries from the file.

In some scenarios we have following construct in sql server where we are checking the value and depending upon it running one of the 2 sql's:

If (values = 'xyz')

Begin

Select....

End

Else

begin

Select.....

End  

Oracle somehow don't like this construct and giving error : ORA-00900: invalid SQL statement  

What is the way to port this to oracle?  

Thanks

--Harvinder
 

--

---

http://iyertalks.blogspot.com/

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 15 2006 - 10:09:01 CDT