w0051977 w0051977 - 4 years ago 108
SQL Question

Scalar Valued function - output table

I have recently installed Master Data Services on an instance of SQL Developer and I am now able to run the following query when the MDS database is selected in SQL Studio Manager:

SELECT mdq.Similarity(N'Alexia Geogio', N'Alexandra George', 0, 0.0, 0.0);


Now, please see the DDL below:

create table #Person (ID int not null identity, Name varchar(100), primary key (ID))
INSERT INTO #Person (Name) values ('Alexia Geogio')
INSERT INTO #Person (Name) values ('Alexandra George')
INSERT INTO #Person (Name) values ('Ian')
INSERT INTO #Person (Name) values ('Iain')


Is it possible to get an output like this with an SQL statement (three columns):

Alexia Geogio Alexandra George 0.5625
Alexandra George Alexia Geogio 0.5625
Ian Iain 0.75
Iain Ian 0.75


mdq.Similarity is a scalar valued function for anyone who did not know.

I believe that as it is not a table valued function, then there is nothing I can do.

Answer Source

I think a query like this does what you want:

select name1, name2, similarity
from (select pt1.name as name1, p2.name as name2,
             mdq.Similarity(p1.name, p2.name, 0, 0.0, 0.0) as similarity
      from #person p1 cross join
           #person p2
     ) pp
where similarity > @SimilarityLimit  -- perhaps 0
order by similarity;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download