Parsing field value based upon derived dataset

Experts

I am attempting to CASE a column in a view based upon a select against a separate table.

The select will return a number of distinct values; this will then be used to CASE the column in the view. This column will contain space-separated values in the form '1 2 3 4 5 A C D', etc

Consequently, if the select statements returns values of 1,2,3 and any or all of these values appears in the view column, the column should be updated as 'COMPLIANT' for example.

We're no doubt looking at a UDF here but any pointers you could offer would be appreciated.

Thanks


JC
campbell1972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
>>space-separated values

That design is a violation of first normal form -- and makes stuff like this MUCH harder.  Is correcting the design an option at this point?

Assuming it is not ... perhaps something like ...

Select CASE When (Select count(*) From MainTable Inner Join otherTable on MainTable.SpaceSeparatedColumn like '%' + otherTable.DistinctValue + '%') > 0 then 'COMPLIANT' else 'Not Compliant' END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
campbell1972Author Commented:
Hi Daniel

Unfortunately the design is inherited and due to pressure of timescale there is very little I can do about that.

However, what you have suggested does sound like a possibility. I'll investigate further and get back to you.


JC
0
Daniel WilsonCommented:
OK, if you need code that more closely fits your table structure, please post that structure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.