proper method for testing for a value in a column on a table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Apr 2003
    Posts
    2

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

    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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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;

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Tom as in Tom Kyte? What the heck does he know anyway?

  5. #5
    Join Date
    Apr 2003
    Posts
    2
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by stecal
    Tom as in Tom Kyte? What the heck does he know anyway?
    Good point. forget i mentioned it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    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.
    Last edited by stecal; 04-07-2003 at 05:37 PM.

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Here's a handy function for ya:

    Code:
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width