-
Verify value exist in table...
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.
-
You've not said which version so I've assumed 9i in which the following will work.
Code:
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
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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.
-
Thanks slimdave...exactly what I was looking for!!
-
-
Hey, good catch Pando. Does that mean that Tom Kyte is copying my answers as well!?!?!?!?!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|