{\rtf1\ansi\ansicpg1252\deff0\deflang1036{\fonttbl{\f0\fswiss\fcharset0 Arial;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs20 CREATE FUNCTION f_toqe_getConcept (@NameId INT, @RefId INT)\par RETURNS @Results TABLE (NameFk1 INT ,RefFk1 INT , RelQualifierFk INT,Sens VARCHAR(15),NameFk2 INT,RefFk2 INT,Cle VARCHAR(100))\par AS\par \tab BEGIN\par \par DECLARE @CurrentTaxo INT\par DECLARE @CurrentStatus INT\par DECLARE @AcceptedName INT\par \par \par SELECT @CurrentStatus=StatusFK FROM PTaxon WHERE PTNameFK = @NameId and PTRefFK = @RefID;\par \par --Status 1 = accepted\par \par IF @CurrentStatus !=1\par --getAcceptedName First\par \tab BEGIN\par \tab SELECT @AcceptedName = PTNameFK2 FROM RelPTaxon WHERE PTNameFK1=@NameId AND PTRefFK1=@RefId AND RelQualifierFK IN (2,3,4,5,6,7) \par \tab END\par \par \par \tab IF @CurrentStatus =1\par \tab\tab BEGIN\par \tab\tab INSERT INTO @Results \par \tab\tab SELECT PTNameFk2, PTRefFK2 , RelQualifierFk , '_inverse', PTNameFk1, PTRefFk1 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR)\par \tab\tab FROM RelPTaxon\par \tab\tab WHERE (PTNameFk1=@NameId AND PTRefFk1 = @RefId )\par \tab\tab AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par \par \tab\tab INSERT INTO @Results\par \tab\tab SELECT PTNameFk1, PTRefFK1, RelQualifierFk, '', PTNameFK2, PTRefFk2, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR)\par \tab\tab FROM RelPTaxon\par \tab\tab WHERE (PTNameFk2 = @NameId AND PTRefFk2 = @RefId )\par \tab\tab AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par \tab\tab END\par \tab IF @CurrentStatus !=1\par \tab\tab BEGIN\par \tab\tab\par \tab\tab INSERT INTO @Results \par \tab\tab\tab SELECT PTNameFk2, PTRefFK2 , RelQualifierFk , '_inverse', PTNameFk1, PTRefFk1 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR)\par \tab\tab\tab FROM RelPTaxon\par \tab\tab\tab WHERE (PTNameFk1=@AcceptedName AND PTRefFk1 = @RefId )\par \tab\tab\tab AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par \tab\tab INSERT INTO @Results\par \tab\tab\tab SELECT PTNameFk1, PTRefFK1, RelQualifierFk, '', PTNameFK2, PTRefFk2, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR)\par \tab\tab\tab FROM RelPTaxon\par \tab\tab\tab WHERE (PTNameFk2 = @AcceptedName AND PTRefFk2 = @RefId )\par AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par \tab\tab END\par \par RETURN\par \par END\par \par \par \par \par \par \par \par }