Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Red face Unanswered: Select Query having IN keyword

    Hi All,

    I have created so many queries for DB2 and successfully executed it.
    I am facing problem in executing query which uses IN keyword.

    I have a text box on page where user can enter any number of comma separated values. I need to query DB using this parameter as

    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb);

    Here, stateAbb value I will pass from my java program.
    Lets say user has entered in text box two values - DC, AL.

    In my java program I am taking these values one by one and making it as 'DC' and 'AL' and again appending these as 'DC', 'AL' because in query it must be in single quotes.
    So, my query became (printed in console):
    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');


    Now, when I send it and execute this, I get the following error :
    nested exception is java.sql.SQLException: An undefined column name was detected

    The same query when I run directly on sql page ..it runs successfully.

    I posted the same problem in Java forums, in one reply I got that JDBC will not accept 'DC','AL' because you are sending it as a string.

    I do not know why so?

    If anybody have any idea or solution, please let me know.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is that you are trying to pass multiple items in one host variable. This cannot work. You are going to have to dynamically build the query, then execute it. You cannot use the host variable method to do this.

    Andy

Posting Permissions

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