Solved

Parsing field value based upon derived dataset

Posted on 2008-10-23
5
253 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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