Verify value exist in table...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Verify value exist in table...

  1. #1
    Join Date
    Dec 2002
    Posts
    4

    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.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

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

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

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Posts
    4
    Thanks slimdave...exactly what I was looking for!!

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    didnt you ask this?

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

    same text, cut & pasted

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hey, good catch Pando. Does that mean that Tom Kyte is copying my answers as well!?!?!?!?!
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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