Re: Simple SQL request

From: Jean-Baptiste <_at_@b2i.fr>
Date: Fri, 14 Jan 2005 14:39:37 +0100
Message-ID: <41e7cb93$0$10265$626a14ce@news.free.fr>

Mark C. Stock wrote:

> "Bavoux Jean-Baptiste" <jbb1_at_laposte.net> wrote in message 
> news:41e7ac4a$0$10272$626a14ce_at_news.free.fr...
> 

>>My need is to search if there is at least one recordset in a table.
>>(from a procdure in Oracle)
>>
>>My solution is:
>>
>>my_count PLS_INTEGER;
>>
>>...
>>
>>SELECT COUNT(*) INTO my_count FROM my_table WHERE (my_filters ...);
>>IF (my_count >= 1) THEN
>>-- ok
>>ELSE
>>-- nok
>>ENDIF
>>
>>The probleme is that i don't need to count them all.
>>Il my table has 1 000 0000 recorset and the half match my filters
>>it will be very slow even if the first recorset matchs my filters.
>>
>>I ve try to do thinks like:
>>
>>CURSOR my_cursor IS
>> SELECT 1
>> FROM my_table
>> WHERE (my_filters ...);
>>
>>BEGIN
>>
>> OPEN EMP_CUR; -- open the cursor
>> FETCH EMP_CUR INTO v_FoundOne;
>> CLOSE EMP_CUR;
>>
>>IF (v_FoundOne IS NOT NULL) THEN
>>-- ok
>>ELSE
>>-- nok
>>ENDIF
>>
>>It works but I find that the code i not very simple.
>>
>>Is there another solution?
>>Something like SELECT FIRST(*) FROM my_Table WHERE ...

> 
> 
> look into cursor for loops or 'where exists' sub-queries
> 
> ++ mcs 
> 
> 

temp PLS_INTEGER;
BEGIN

	 SELECT 1 INTO temp FROM DUAL
	 WHERE EXISTS
	( SELECT * FROM my_table WHERE (my_filters...);)
	IF (temp IS NULL) THEN
		...
	ELSE
		...

This solution seems as fast as the CURSOR method. The syntax is simplier than CURSOR method, (not obliged to declare a cursor)
  but still complicated... (may be it can be simplified) I would have prefer

        IF EXISTS ( SELECT * FROM my_table ..;) but this doesn't work.

The method with ROWNUM doesn't work because ROWNUM is the row number from the table and not from the result.
There is a similar methode which works with mySQL with the LIMIT key word but not in oracle.

Thanks. Received on Fri Jan 14 2005 - 07:39:37 CST