Solved

Parsing field value based upon derived dataset

Posted on 2008-10-23
5
251 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:campbell1972
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22786058
>>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
 

Author Comment

by:campbell1972
ID: 22786126
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22786380
OK, if you need code that more closely fits your table structure, please post that structure.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
Make query more efficient 1 21
Query still returning duplicates 5 32
average of calculation (TSQL) 4 9
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question