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

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

your three cases can be reduced to one as follows --
note that i chose to use the ANSI SQL concatenator, seeing as this is the ANSI SQL forum

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 ;-))

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

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

Similar issue was discussed in here
Performance Issue: 200 columns versus Single column

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