Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Unanswered: using a variable for IN statement

    Hi,

    I am on an Oracle 10g database on a Unix server. I have a table with 2 string fields and one of them has several values such as A, B, C, D. In addition, I have a procedure in which I am retrieving that string field into a VARCHAR variable. I now want to use this variable in a select statement so it would look like this:

    Code:
    SELECT strfld
       INTO lv_letter_list
       FROM tblname;
    
    SELECT *
       FROM table2 
     WHERE 'A' IN (lv_letter_list)
    However, that is not working because I assume Oracle is adding quotes so how do I get this to work? I can't place my values in a look up table, I am stuck with the table that has the string field. All I can do is manipulate how I enter the data inside the string field, ie with or w/o quotes or with or w/o commas.

    Any help is greatly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    Thanks - I looked at that code, but at the end, in the select statement where he is calling the in_list function, isn't he still hard coding? It's the hard coding I need to avoid.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The only way I know to handle different values "on the fly" is via "Dynamic SQL" & (ab)using EXECUTE IMMEDIATE.
    This approach scales as well as a pig can fly.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2007
    Posts
    14
    So there is no other way to do this? I tried creating the table type, function, etc. and the select statement then gives me the values as if they were in a table.

    COLUMN_NAME
    A
    B
    C
    D

    What I am having trouble with is calling the function and using the select statement inside my procedure. More specifically, I am trying to is it in an IF statement like
    Code:
    IF lv_letter IN (lv_letter_list) THEN
    But no matter how I code it I am not getting it to work. I don't see anywhere on Tom's explanation how he uses it inside a procedure unless I missed it.

    Thanks!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If it's really just a string of letters, then:
    Code:
    IF INSTR (letter_list, 'A') > 0 THEN ...

  7. #7
    Join Date
    Nov 2007
    Posts
    14
    Thanks - that worked and was a lot simpler.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Or you can insert that list of values into a global temporary table, and then your SQL would look like:

    Code:
    select * 
    from some_table st
    where st.field in (select other_field from global_temp_table)

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kkc
    Thanks - I looked at that code, but at the end, in the select statement where he is calling the in_list function, isn't he still hard coding? It's the hard coding I need to avoid.
    It depends on what exactly you mean with "hard coding".
    For me, it is using literal values instead of binding them in dynamic SQL. As the SELECT statement is static, this is not the case. Tom is using string constant as parameter for IN_LIST for demonstration, but it may replace it with string variable in your case.

Posting Permissions

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