proper method for testing for a value in a column on a table

[paccon]

Hello, I've been a programmer of many years but this is my first time with PL/SQL so please excuse the naivety of my question.

In an "INSTEAD OF" trigger on a view I'm trying to test a column in a different table for the existence of a value and then based on that result I'll insert the data in one table or another. This seems to me to be a trivial task but the only way I've found to do this is to "select into" on the table and then catch the resulting exception. This works but it's a kludge, and it really falls apart with compound decisions - especially if NO_DATA_FOUND is the true condition.

So my question is; what is the correct way to do this? I know I'm missing something fundamental here, but to me it seems it should be a simple as "if A exists in B.C then ..."

Thanks for any help you can offer,
Matt

[stecal]

DECLARE
v_count number;

BEGIN
select count(whatever) into v_count
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
-- you can add this if all you want to do is check for existence
-- if having just one is good enough, use this
-- or you can compare v_count directly to any number
and rownum =1;

IF v_count > 0 THEN
-- meaning the conditions you are looking for exist
do_the_voodoo_you_do_as_a_programmer;
update table_name set ...
insert into table_name ...
etc;
ELSE
do_whatever;
END IF;

END;

[slimdave]

to check for existence, an alternative is ..

select 1 from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);

I think there was something on AskTom about it recently

[stecal]

Tom as in Tom Kyte? What the heck does he know anyway?

[paccon]

Thanks Steve,

Using the count make sense to me. I had thought that methods like "count" and "exists" could only operate on collections - not tables. I guess I didn't understand the doc's correctly, or had the syntax wrong. Anyway this is MUCH better than my approch, trying to use exceptions for logic was making me quezy

Matt

[slimdave]

Originally posted by stecal
Tom as in Tom Kyte? What the heck does he know anyway?

Good point. forget i mentioned it.

[slimdave]

Here it is.

Actually he suggests ...

select count(*) from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);

...since it always returns one row.

[stecal]

That works as far as output is concerned, but for PL/SQL usage, you still need a collector or variable to store the result so dependent processing can occur (the IF-THEN-ELSE based on the result stored in the variable). The output from your suggestion would have to be "selected into" for it to have any meaning in PL/SQL.

[slimdave]

of course, in which case ...

declare
my_number number;
begin
select count(*) nito my_number from dual where exists
(
select 1
from the_table_or_view
where condition_is = what_you_are_looking_for
and other_condition_is = whatever_else_you_are_looking_for
and so on
);
if my_number = 1
then
null;
else
blah blah blah ...

[chrisrlong]

Here's a handy function for ya:

CREATE OR REPLACE FUNCTION Exists_sf
/*****************************************************************************
**	FUNCTION:	Exists_sf
**
**	DESC:	This function allows one to do an IF Exists_sf(),
**		much like one can do in SQLServer. Since the statement is a string,
**		single quotes must be 'doubled' and binds are not presently handled.
**		Still a fairly useful piece of functionality.
**
**	USAGE:

	DECLARE
		l_TABLE_NAME	VARCHAR2(30);
	BEGIN
		l_TABLE_NAME	:=	'XAPLCN_DEBUG';
		IF ( Exists_sf('
					SELECT
						1
					FROM
						USER_TABLES
					WHERE
						TABLE_NAME	=	'''||
						l_TABLE_NAME||''''
					)
			)
		THEN
			DBMS_OUTPUT.PUT_LINE ('Does exist');
		ELSE
			DBMS_OUTPUT.PUT_LINE ('Does not exist');
		END IF;
	END;

**
**	HISTORY:	NAME						DATE		COMMENT
**				--------------------	--------	------------------------------------
**				Christopher R. Long	04/2001	Initial Code
**
*****************************************************************************/
	(
	i_SQL			IN			VARCHAR2	
	)
RETURN
	BOOLEAN
IS
	-- ---------------
	-- Local Variables 
	-- ---------------
	l_SQL				VARCHAR2(32767);
	l_Exists_Flg	CHAR(1)			;

BEGIN

	l_SQL :=	'
		SELECT
				-- Exists_sf
			COUNT(*)
		FROM
			DUAL
		WHERE
			EXISTS
				(
				'||i_SQL||'
				)	';

	EXECUTE IMMEDIATE
		l_SQL
	INTO
		l_Exists_Flg	;

	RETURN l_Exists_Flg = 1;
 
END Exists_sf;

And, as always, a 3-character tab makes it all line up nicely

- Chris