Skip to main content
A
Anonymous
June 29, 2007
Answered

QoQ and ANSI SQL Operator "IN"

  • June 29, 2007
  • 2 replies
  • 340 views
Howdy,

I'm trying to get a query subset based on a specific value existing in a "list" field of the main query. Please see code. Is it possible to test for that value in the list as I show in the code? I've already tried it and it doesn't, but I'm hoping it's a minor grammar issue that I missed in the tests.

It worked if my where statement was: where distribution_lists LIKE '%test%', but I want to test for the specific value 'test', and that doesn't work, either.

Thank you for any ideas.
    This topic has been closed for replies.
    Correct answer paross1
    The expression 'test' in (distribution_lists) won't work, since distribution_lists is actuall a database column, and 'test' is a constant, and that is invalid SQL. Using a column in a table to store a list of values is generally a bad data model practice, and you are seeing the real world consequences of such a design. I would recommend normalizing your data and migrate your distribution_lists to a table where you can have individual list values in your column.

    Phil

    2 replies

    P
    paross1Correct answer
    Participating Frequently
    June 29, 2007
    The expression 'test' in (distribution_lists) won't work, since distribution_lists is actuall a database column, and 'test' is a constant, and that is invalid SQL. Using a column in a table to store a list of values is generally a bad data model practice, and you are seeing the real world consequences of such a design. I would recommend normalizing your data and migrate your distribution_lists to a table where you can have individual list values in your column.

    Phil
    A
    Anonymous
    June 29, 2007
    I was just starting to think that's what it would need to be. Though it's unfortunate that you can't parse-out and compare list values in the fashion I tried. I'm guessing that there's too much processing overhead for that approach.

    Thank you for the input!
    C
    cf_dev2
    Inspiring
    June 29, 2007
    Is distribution_list a comma delimited list of values by any chance?
    A
    Anonymous
    June 29, 2007
    Yes - it is. My apologies. I should have mentioned that. In some cases the distribution_lists field will be empty. Sometimes there will just be one value. Sometimes a whole list of values.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices