rowcount values

rowcount values

Post by v.. » Sat, 15 Jan 1994 03:51:43



Hai,

I was trying a couple of queries and the result in the rowcount is very         confusing.

The queries I were trying are the following:-

1> select * from Ir_lot_track
2> where ir_nbr=14401 and sterile_batch_nbr=null
3> and status_code=9
4> go
 ir_nbr      sterile_batch_nbr item             lot_nbr    ops_seq ops_nbr
         ops_station date_time_in               assoc_code_in
         date_time_out              assoc_code_out time_delay  status_code
         qty_in qty_out
         comments

         assoc_code_upd date_time_upd
 ----------- ----------------- ---------------- ---------- ------- -------
        ----------- -------------------------- -------------
        ------        -------------- --------------------------

(0 rows affected)

Second query:-



3> where ir_nbr=14401 and sterile_batch_nbr=null
4> and status_code=9
5> go
(1 row affected)

2> go

 -----------
           1

(1 row affected)

Basically, both my queries are same except for the fact that I am
introducing a variable in the second query. However, my second
query seems to be returning " I row affected" which I think might be
due to the using of a variable. I guess its returning null as a row
affected. Can someone please explain whats happening here?
If  I  dont want this to hapen  
IS there a way to explicitly say in the where clause that the
value to be checked " is numeric or something similar"  and null is not be
counted while doing the checking?

Thankyou. Any suggestions or comments will be very helpful
vishu

--
The road to success is presently under construction.

 
 
 

rowcount values

Post by cwhit » Fri, 14 Jan 1994 22:28:15


In the first querry, you are selecting *, which may or may not have any matching rows. In the second, you are selecting an aggregate ("min(---)").  Aggregates *always have 1 and only 1 row returning*. Try the select without the aggregate, as in:



3> where ir_nbr=14401 and sterile_batch_nbr=null
4> and status_code=9
5> go

and see what happens.

Topher
---
Christopher A. White          | "I once could see,
McCaw Cellular Communications |      but now, at last, I'm blind."
Kirkland, Wa                  |         -- Dream Theatre

 
 
 

rowcount values

Post by Paul Ch » Sat, 15 Jan 1994 07:37:43




>Hai,

>I was trying a couple of queries and the result in the rowcount is very         confusing.

>The queries I were trying are the following:-

>1> select * from Ir_lot_track
>2> where ir_nbr=14401 and sterile_batch_nbr=null
>3> and status_code=9
>4> go
> ir_nbr      sterile_batch_nbr item             lot_nbr    ops_seq ops_nbr
>         ops_station date_time_in               assoc_code_in
>         date_time_out              assoc_code_out time_delay  status_code
>         qty_in qty_out
>         comments

>         assoc_code_upd date_time_upd
> ----------- ----------------- ---------------- ---------- ------- -------
>        ----------- -------------------------- -------------
>        ------        -------------- --------------------------

>(0 rows affected)

>Second query:-



>3> where ir_nbr=14401 and sterile_batch_nbr=null
>4> and status_code=9
>5> go
>(1 row affected)

>2> go

> -----------
>           1

>(1 row affected)

>Basically, both my queries are same except for the fact that I am
>introducing a variable in the second query. However, my second
>query seems to be returning " I row affected" which I think might be
>due to the using of a variable. I guess its returning null as a row
>affected. Can someone please explain whats happening here?
>If  I  dont want this to hapen  
>IS there a way to explicitly say in the where clause that the
>value to be checked " is numeric or something similar"  and null is not be
>counted while doing the checking?

>Thankyou. Any suggestions or comments will be very helpful
>vishu

>--
>The road to success is presently under construction.


the presence of min() function in Query II.  Min() ALWAYS returns
something (it returns NULL when operating on the empty set.)  
Since min() always returns something, there is no way to make the
rowcount zero in Query II as long as min() is there.

Paul Chen, Ph.D.  on assignment at Fidelity Investments
-------------------------------------------------------

-------------------------------------------------------
Disclaimer: My opinions only!

 
 
 

1. @@ROWCOUNT value when using INTEAD OF trigger for update (2000)

Hi all!
I'm using SQL Server 2000 and INSTEAD OF Trigger for update.


trigger executes.

The result is that ODBC throws en exception and rolls back the action.

When I'm using ADO it works fine only when I use adUseClient cursor location
on the connection.

I really need it to work with ODBC but my experience in ODBC is little.

I cant find a way to fix this in SQL Server or in ODBC API

-----------------------------
P L E A S E    H E L P
------------------------------
Thank

Ramon Chemel

2. ActiveX component can't create object

3. Getting the last SET ROWCOUNT value

4. SP3 install

5. Reset a rowcount based on a value?

6. Scheduler 0 appears to be hung

7. SET ROWCOUNT inside of a user-defined function

8. 4725-CA-Bay Area-ORACLE-Communication Skills-Financial Applications-Application Expert

9. Using @@ROWCOUNT/ median function

10. set rowcount 500000

11. PROBLEM with SET ROWCOUNT

12. set rowcount and cursor, which one performs better?

13. Sql Server SELECT & SET ROWCOUNT