Re: no rows selected vs. '0' count

A copy of this was sent to oct1pm_at_hotmail.com (if that email address didn't require changing) On Thu, 30 Sep 1999 15:45:43 GMT, you wrote:

>Hi, can anyone help me solve this puzzle, please ?
>
>The below query returns no rows.
>
> select count(*),AVG(DURATN),create_id from mcis_load_time
> where to_char(strt_ts)='30-SEP-99' and create_id='1EQHBHAT'
> group by create_id
>
>no rows selected
>
>If I remove the create_id from selection list. And also I remove the
>group by clause then the count(*) returns 0.
>
> select count(*),AVG(DURATN) from mcis_load_time
> where to_char(strt_ts)='30-SEP-99' and create_id='1EQHBHAT'
>
>
> COUNT(*) AVG(DURATN)
>--------- -----------
> 0
>
>
>Is there any way that I can force the first query to return some value
>so that I know that "no rows selected".
>

No -- you'd be asking us to 'make up' data.

When you GROUP BY something, it takes the result set and returns a row for each Unique group by element. If you don't have any rows -- there is nothing to group by, hence NO data.

given your above queries though, I think you want to select some constant and the count/avg right? If so, the following will do it:

tkyte_at_8i> variable b1 number
tkyte_at_8i> 
tkyte_at_8i> exec :b1 := 10

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> select :b1, count(*), avg(sal)   2 from emp
  3 where deptno = :b1
  4 /

       :B1 COUNT(*) AVG(SAL)
---------- ---------- ----------

        10 3 2916.66667

tkyte_at_8i> exec :b1 := 1000

PL/SQL procedure successfully completed.

tkyte_at_8i> select :b1, count(*), avg(sal)   2 from emp
  3 where deptno = :b1
  4 /

       :B1 COUNT(*) AVG(SAL)
---------- ---------- ----------

      1000 0

instead of selecting and grouping by the column you WHERE on -- include the vlaue you put in the WHERE clause in the select list...

>thanks,
>
>Please email me also at: abhatt_at_shl.com
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 30 1999 - 12:37:12 CDT