Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: SQL Query suggestion when multiple values are stored in 1 column

    Hi All,

    I have a column called CountryCdList that has got multiple values stored in it for a row.
    For E.g. - AR,CL,CO

    I have to write a query based on below dynamically generated WHERE clause based on the profile access to the User.

    ( U.CountryCdList = 'IND') OR ( PG.CountryCdList = 'IND')

    OR

    ( U.CountryCdList IN ('IND','JP)) OR ( PG.CountryCdList IN ('IND','JP'))

    my query works fine if there is only 1 value stored in the CountryCdList column but fails in the above scenario. Please guide me.

    Thanks,
    Sheetal

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My first reaction is that you have a rather strange design. You may want to consider normalizing the information, i.e. break the values in CountryCdList into multiple rows in another table.

    If you can't do that, you could either parse the content of each value like shown here Parsing Strings in SQL (specific for DB2 but pretty close to standard SQL) and then use a predicate like
    Code:
    'IND' IN ( SELECT country FROM LATERAL ( elements(CountryCdList) ) AS t(country)
    Another alternative is to use the LIKE predicate. For this you'll have to consider the 3 cases that the sub-value is at the beginning, end, or in the middle.
    Code:
    CountryCdList LIKE 'IND,%' OR CountryCdList LIKE '%,IND,%' OR CountryCdList LIKE '%,IND'
    For all of this, you shouldn't have much data in the table or use other filtering predicates because the query may be a bit slow.
    Last edited by stolze; 01-28-12 at 13:40.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your three cases can be reduced to one as follows --
    Code:
    WHERE ','||CountryCdList||',' LIKE '%,IND,%'
    note that i chose to use the ANSI SQL concatenator, seeing as this is the ANSI SQL forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Good point Rudy! From a product-specific point of view, you'd probably want to treat "... LIKE 'IND,%'" and "... LIKE '%,IND,%' separately because the latter would allow easier index support. But then, the respective optimizer may not choose an index due to the OR-predicate. (And from a standard SQL perspective, indexes don't exist to begin with ;-))
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2009
    Posts
    3
    I am still not sure if the above approach will work on the data that is stored in the tables.

    For eg - In CountryCdList column data stored is AR,CL,CO whereas user has the access to SPP,IND,AR

    So, comparison will be between AR,CL,CO and SPP,IND,AR. I have return x if all the values from left side is present in the right side or return y if some of the value are present in right.


    thanks,
    sheetal

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So you have 2 sets of country codes, which you want to compare for equality and/or containment? Err... why are you using strings for that? You should seriously reconsider your database design; it seems to be flawed.

    For your case, you could
    (1) parse both strings (user input and the values in the rows) into multiple rows like the "elements" function does
    (2) compare the temporary tables built that way

    This would be one approach for containment, i.e. all elements in the user input must be in the CountryCdList for the row in your table to qualify. What the query does is to join the tables derived from parsing user input and CountryCdList via an outer join. If there is a row having NULL for tableData.country, it means that no matching row country code was found in CountryCdList. That row is return and the NOT EXISTS in the outer query negates the result.
    Code:
    SELECT ...
    FROM   ...
    WHERE    NOT EXISTS ( SELECT 1
                          FROM   LATERAL ( elements(<user-input>) ) AS userInput(country) LEFT OUTER JOIN
                                 LATERAL ( elements(CountryCdList) ) AS tableData(country)
                                 ON userInput.country = tableData.country
                          WHERE  tableData.country IS NULL )
    For equality (all country codes in the user input must match all CountryCdList - and vice versa!), you can use a FULL OUTER JOIN and check for "userInput.country IS NULL OR tableData.country IS NULL".

    Basically, the query fixes the data modeling problem at run-time by building the temporary tables via a table function. I doubt that this will be very efficient, but it should at least give you the desired results.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Similar issue was discussed in here
    http://www.dbforums.com/oracle/16733...le-column.html

    Please see my Example 1, Example 2 and Example 3.
    To ask multiple values, HAVING caluse was used in EXISTS subquery.

Posting Permissions

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