Re: SELECT statement issue

Where are you writting this ...

On 9/15/06, Harvinder Singh <Harvinder.Singh_at_metratech.com> 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_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://iyertalks.blogspot.com/

--
http://www.freelists.org/webpage/oracle-l