Stpete111 January 2017

SQL Query - add NOT NULL condition to either of two columns

I am creating a SQL Procedure in MS SQL as follows:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)
AS
BEGIN
SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
order   by s.MaxRank desc

END

The above executes successfully but includes some results where the Text field is the only field with data, with the others being NULL. I want to add a condition that says that, in the returned results, IndividualName must not be null OR EntityName must not be null.

I'm having trouble figuring where to put this and the exact syntax.

Answers


JohnHC January 2017

I assume you mean that one of the fields CAN be null. If so:

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text

from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group   by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
where not (c.IndividualName is null and c.EntityName is null) -- excludes results where both are null
order   by s.MaxRank desc
END


DVT January 2017

CREATE PROCEDURE [dbo].[SearchData] 
@searchText varchar(500)

AS BEGIN SET NOCOUNT ON

select top 50 s.[MaxRank] as [Rank],
    sc.TaskSourceId,
    c.IndividualName,
    c.EntityName,
    c.Text
from    (
    select  max(s.Rank) as MaxRank,
        max(c.CaptureId) as MaxCaptureId,
        c.TaskSourceId
    from    FreeTextTable(Data.SearchData,
           (Identifier, IndividualName, EntityName, [Text]),
               @searchText) s
    join    Data.Capture c
    on  s.[Key] = c.CaptureId
    group by c.TaskSourceId
    ) s
join    Data.Capture c
on  c.CaptureId = s.MaxCaptureId AND (c.IndividualName IS NOT NULL OR c.EntityName IS NOT NULL)
join   Data.Source sc
on     c.TaskSourceId = sc.TaskSourceId
order by s.MaxRank desc

END

Post Status

Asked in January 2017
Viewed 3,864 times
Voted 5
Answered 2 times

Search




Leave an answer