Solved

Parsing field value based upon derived dataset

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now