RE: sql%found problem

If you actually need to know the value the using select from dual where exists
will not work for you. Its basically used where you really don't care about any
of the data, just that there is actually some data there.

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of paul Sent: Tuesday, July 18, 2000 2:48 PM
To: Multiple recipients of list ORACLE-L Subject: Re: sql%found problem

hi kimbwerly,

i will try this .its very new to me.could u tell me how it works.  actually my query should be like
selcec max(no) from table name where =primary key. if it exists i have to do some operations else do some other,

thanks
paul
-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tuesday, July 18, 2000 1:37 PM

>Actually, I have never performed any tests on this but I was told to do the
>following select (assume cursor)
>select 1 from dual where exists (select 1 from str);
>The reasoning given to me is that just select 1 from str still checks
>the whole table where as using the dual table kicks it out as soon as it
>find the first record. Has anyone else been told the same or things that
>this
>is indeed true?
>
>-----Original Message-----
>Kevin M
>Sent: Tuesday, July 18, 2000 7:25 AM
>To: Multiple recipients of list ORACLE-L
>
>
>It may just be the sample code, but I'm wondering why you're using the
COUNT
>function to perform an existance check. I've never known a case where that
>is the fastest way to do this.
>
>A better way to do an existance check is
>
>DECLARE
> CURSOR c_exists IS
> SELECT 1
> FROM str;
> v NUMBER(1);
>BEGIN
> OPEN c_exists;
> FETCH c_exists INTO v;
> IF (c_exists%FOUND) THEN
> DBMS_OUTPUT.PUT_LINE('Yes');
> ELSE
> DBMS_OUTPUT.PUT_LINE('No');
> END IF;
>END;
>
>However, if you do need to know the number of rows that meet your criteria,
>you have to do the COUNT().
>
>Kevin
>>
>> HAI all,
>>
>> The problem not with SQL%found ,problem with count function.
>>
>> If you use count function definitely it will return value
>> Zero or more than
>> zero.
>> It means SQL Stmt executed successfully and returning rows
>> also,so It will
>> never go to 'No' section in your coding .
>>
>> By the way for your information
>> If you used Count funtion in SQL Stmt You no need to define
>> Exception WHEN
>> NO_DATA_FOUND since the count function never return 'no rows';
>>
>>
>> Regards.
>> SUL.
>>
>>
>> ----- Original Message -----
>> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>> Sent: Tuesday, July 18, 2000 06:35 AM
>>
>>
>> > Select count statement always returns a number whether
>> there exists rows
>> in the table or not.
>> >
>> > If there are no rows the outcome is 1 row with count as 0.
>> > If there are some rows in the table output is 1 row with
>> count of Number
>> of rows.
>> > As always there is a row returned SQl %found is always TRUE
>> > So as per ur logic, it will always go to YES part...
>> >
>> > Saji
>> >
>> > ---------------------------------------- Message
>> History ----------------------------------------
>> >
>> >
>> >
>> > Please respond to ORACLE-L_at_fatcity.com
>> >
>> > To: ORACLE-L_at_fatcity.com
>> > cc:
>> >
>> >
>> >
>> > hi all,
>> > i had a problem over this query.could u tell me what is
>> wrong in that
>> query.
>> > ========
>> > declare
>> > v number(10);
>> > begin
>> > select count(name) into v from str;
>> > if sql%found then
>> > dbms_output.put_line('yes '||v);
>> > else
>> > dbms_output.put_line('no '||v);
>> > end if;
>> > end;
>> > it displays only "yes'' even though the table contins or
>> not contain any
>> rows.
>> > could u tellme what went wrong.
>> > paul
>> >
>> >
>> >
>> >
>> > --
>> > Author:
>> > INET: saji.rajp_at_db.com
>> >
>> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> > San Diego, California -- Public Internet access /
>> Mailing Lists
>> >
>> --
>> Author: Sultan
>> INET: ssyed_at_fine.co.ae
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing Lists
>--
>Author: Toepke, Kevin M
> INET: ktoepke_at_cms.cendant.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>--
>Author:
> INET: Kimberly.smith_at_gmd.fujitsu.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>

--
Author: paul
  INET: pjohnpeter_at_qssnet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L