dbo.GetSearchTerms.StoredProcedure.sql
4.37 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetSearchTerms] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: magic
-- Create date: 19/5/2009
-- Description: Fetch Editions for building level accounts for given Account Number and SiteId.
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSearchTerms]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSearchTerms]
GO
CREATE PROCEDURE [dbo].[GetSearchTerms]
AS
BEGIN
IF 1=0 BEGIN
SET FMTONLY OFF
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select * from (
Select CASE WHEN trm.SearchText is not null THEN trm.SearchText ELSE phr.SearchText END as SearchText,
isnull(trm.TID,0) as VocabTermId, isnull(phr.PID,0) as PhraseId,
(CASE WHEN trm.LID IS NOT NULL THEN trm.LID ELSE phr.LID END) as LexiconId
From
(Select vt.TermText + 'T' as SearchText,max(vtn.TermNumber) as TID, vt.VocabLexiconId as LID
From dbo.VocabTerms vt
INNER JOIN dbo.VocabTermNumbers vtn on vt.TermNumber=vtn.TermNumber
INNER JOIN dbo.VocabLexicon vl on vt.VocabLexiconId = vl.Id
Where vtn.IsCurrent='Y' and vl.IsCurrent = 'Y'
Group by vt.TermText + 'T', vt.VocabLexiconId
) trm
FULL OUTER JOIN
(Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 1 as LID
From ContentDescriptor cd
INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId
INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id
Group by sp.Phrase + 'T'
Union
Select sp.Phrase + 'T' as SearchText, max(sp.Id) as PID, 2 as LID
From ContentDescriptor cd
INNER JOIN ContentDescriptorToPhraseMap cdpm on cd.Id=cdpm.ContentDescriptorId
INNER JOIN dbo.SearchPhrase sp on cdpm.SearchPhraseId=sp.Id
Group by sp.Phrase + 'T') phr
ON trm.SearchText = phr.SearchText and trm.LID = phr.LID) ads
Order by LexiconId,SearchText
END
GO