Problem with IN operator
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Problem with IN operator

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Problem with IN operator

    I'm having a strange problem when using IN operator in a query. The following query works in one database which is 9i and does not work(not returning any rows) in another database which is 10g.

    select * from
    where IN
    (select from
    where =
    )

    Another thing is if I replace the view with a table the query returns results in 10g. If I replace IN operator with a '=' the query returns results.

    The question is:

    Am i missing any default setting in database level? Why the query did not return any rows when I use view? Any ideas?

    Thanks in advance,
  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm pretty sure the query you have posted is not the actual offending query.

    IN operator works just fine on Ora10g; look at your code and your data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    My query is like this

    select * from table_name
    where column_name IN
    (select column_name from view_name
    where column_name = value
    )

    It is returns records in another database which is 9i. If I use a table instead of view, I get records.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by lgopalakrishnan View Post
    If I use a table instead of view, I get records.
    Most probably your view is wrong; look at your view.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2000
    Posts
    126

    more info

    It helps if you post create table/insert data and your select statement so we can check it out

  6. 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