Verify value exist in table...

[bse]

Could someone suggest the most CONCISE way to have SQL, NOT PL/SQL to verify that a value exist in a table and if the value exist, continue with the script, otherwise exit?

I know this is a simple matter in PL/SQL, but I'm curious of the method to do it in SQL.

Thanks.

[jovery]

You've not said which version so I've assumed 9i in which the following will work.

SELECT *
FROM table
WHERE (SELECT condition_column FROM condition_table) = condition_to_proceed

eg.

If you have a STATE table with a column TEST and you want to make sure TEST = 1 to proceed your statement would look like 

SELECT *
FROM any_table
WHERE (SELECT test FROM state) = 1;

Hope that helps

[slimdave]

What you may be wanting to do is raise an error if a value exists in a table, or if it doesn't, and let SQL Plus exit on error.
Something like ...

select x from dual where exists(select 1 from my_table where my_column = 'VALUE')

... will be a fast way of detecting whether a value exists in my_table, and ..

select to_number('X') from dual where exists(select 1 from my_table where my_column = 'VALUE')

... will cause an error if the value is found. since you want the opposite, you might like to try ...

select decode(row_flag,0,to_number('X'),null)
from
(select count(*) from dual where exists(select 1 from my_table where my_column = 'VALUE'))

I haven't tested this, and I'm sure there would be variations on the theme.

[bse]

Thanks slimdave...exactly what I was looking for!!

[pando]

didnt you ask this?

http://asktom.oracle.com/pls/ask/f?p...%20sqlerror%7D

same text, cut & pasted

[slimdave]

Hey, good catch Pando. Does that mean that Tom Kyte is copying my answers as well!?!?!?!?!