Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parsing field value based upon derived dataset

Posted on 2008-10-23
5
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 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