RE: SELECT statement issue

  • From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
  • To: "Ganesh Raja" <ganesh.raja@xxxxxxxxx>
  • Date: Fri, 15 Sep 2006 11:54:28 -0400

I was trying this in sqlplus but as explained in first email ,actual
query will be in xml file that the asp.ent code will read through c++
and shows the result of the query on the screen.

 

________________________________

From: Ganesh Raja [mailto:ganesh.raja@xxxxxxxxx] 
Sent: Friday, September 15, 2006 11:13 AM
To: Harvinder Singh
Cc: ORACLE-L
Subject: Re: SELECT statement issue

 

Where are you writting this ... 

 

On 9/15/06, Harvinder Singh <Harvinder.Singh@xxxxxxxxxxxxx> wrote: 

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@xxxxxxxxx] 
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@xxxxxxxxxxxxx
<mailto:Harvinder.Singh@xxxxxxxxxxxxx> > 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://iyertalks.blogspot.com/ 

Other related posts: