Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-11-04 : 05:56:21
|
| I may well regret putting this on here as it may turn out more complex than I hope but the situation I have is this. (I was thinking originally of doing this is some sort of program but having revisited it I'm fairly sure there may be some sort of set based method of getting the results I need.)I have two sets of data that I am trying to compare (not as simple as it sounds initially). I am trying to compare the outputs of two computer programs, (though one set of data the first has to be entered manually!! - nothing I can do about this). This comparison will indicate if the programs are both doing the same thing and that it is what is expected.The first set consists of fieldsSampleIDSeq1Seq2Seq3False+veSFalse-veSThe second set consists of fieldsSampleIDMut1Mut2Mut3Mut4Mut5Mut6Mut7Mut8Mut9Mut10Mut11Mut12Mut13Mut14False+veMFalse-veMAll fields are character type at the momentThe sampleID's match so sample ID 1 in the first set will correspond to sample ID 1 in the second set.The Seq fields will be populated in order i.e. if seq2 is null then seq3 will be. A similar situation does not exist for the mut fields (any - but not all can be null)First problem:-The data in the Mut1,...Mut14 fields is a series of characters (including >/ etc) ending with a comma, then followed by other characters. It may help if this was split into two fields. I think I can do this but if anyone has any suggestions (including not to bother) then I would appreciate themSecond problem:- (and this is the core of what I am trying to do)For each sample ID I need to look at Seq1 and see if it is matched with the start section (i.e. up to the comma) in ANY of the Mut fields (it will either match once only or not at all). I then need to do the same for Seq2 and 3 if they aren't null. Note that Seq1,2 and 3 will never be the same (unless they are null). Similarly Mut1,...14 will never be the same unless they are null.Third problem:- If all the Seq's are matched AND all the False+ve and False-ve fields are NULL THEN we have a match, otherwise the match fails - This is what I want as the ultimate answer for each sampleIDSome Sample DataThe sample data I have (and it's all I have at the moment) is thisMut1 = 1342AC>A,372N/H>N$15Mut5 = 1422-1432insASeq1 = 1342AC>AFalse+veM = 1422-3insAIn this case Mut1 and Seq1 Match however the whole thing is not a match as False+veM isn't NULL.I really do hope this is clear if not please ask for further information. I would appreciate any advice as I am at the very early stages of setting this up and may decide that a set based solution is not the answer and just do it all in code, however, somehow I don't think that is the answerMany many thanks in advancesteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-04 : 06:10:43
|
| Get rid of the stuff after the commas in Mut's or just exclude it from the compare andfrom tb1join tbl2on tb1.SampleID = tbl2.SampleIDwhereseq1 in (Mut1, Mut2, ....)seq2 in (Mut1, Mut2, ....)if you don't remove the stuff after the comma thenleft(Mut1, case when charindex(Mut1,',')-1 > 0 then charindex(Mut1,',')-1) else 0 end)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Out of Order