RE: SELECT statement issue

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: Harvinder.Singh@xxxxxxxxxxxxx
  • Date: Fri, 15 Sep 2006 23:36:28 +0800


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


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


Other related posts: