RE: SELECT statement issue

SELECT COUNT(*) ... always returns 1 row, with 1 value -- a row showing the COUNT.
But SELECT * ... can return 0, 1 or more rows.. The decode expects a single value (which the SELECT COUNT(*) does provide).

Hemant

At 11:09 PM Friday, Harvinder Singh 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
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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