CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Chaz Lee
Chaz Lee

Reputation: 21

Subquery and NOT NULL

I need some help with a subquery. My test column sometimes comes back NULL and if so I want to filter those out of my results set.

My stored procedure looks like this

SELECT
    pl.Id AS Id,
    pl.Name AS Name,
    f.[Url] AS PrimaryImageUrl,
    up.Id AS MemberId,
    up.ProfessionalName,up.
    AvatarUrl,
    test = (SELECT 
                c.Id AS Id,
                c.Name AS Name,
                c.ContentImageUrl AS ImageUrl,
                c.Price AS Price,
                c.BPM AS BPM,
                f.Id AS 'File.Id',
                f.Url AS 'File.Name',
                TotalCount = COUNT (c.Id) OVER()
            FROM
                dbo.Content c
            INNER JOIN 
                dbo.PlayListContents pm ON c.Id = pm.ContentId 
                                        AND pm.PlaylistId = pl.Id
            INNER JOIN
                dbo.Files f ON c.ContentFileId = f.Id
            FOR JSON PATH),
    TotalCount = COUNT(1) OVER()
FROM
    dbo.Playlist pl
INNER JOIN 
    dbo.UserProfiles up ON pl.UserId = up.UserId
INNER JOIN 
    [dbo].[Files] AS f ON pl.[PrimaryImageId] = f.[Id]
WHERE
    (pl.Name LIKE '%' + @searchInput + '%')
    AND test IS NOT NULL

Why is this last line, AND test IS NOT NULL invalid? I need my result set to have all results with test being NOT NULL

Upvotes: 0

Views: 805

Answers (2)

Charlieface
Charlieface

Reputation: 71638

Columns in the SELECT are not available in the WHERE, due to SQL's logical order of operations.

Instead, place the value in CROSS APPLY, then filter after that:

SELECT
    pl.Id AS Id,
    pl.Name AS Name,
    f.[Url] AS PrimaryImageUrl,
    up.Id AS MemberId,
    up.ProfessionalName,up.
    AvatarUrl,
    v.test,
    TotalCount = COUNT(1) OVER()
FROM
    dbo.Playlist pl
INNER JOIN 
    dbo.UserProfiles up ON pl.UserId = up.UserId
INNER JOIN 
    [dbo].[Files] AS f ON pl.[PrimaryImageId] = f.[Id]
CROSS APPLY (
  SELECT test =
    (SELECT 
                c.Id AS Id,
                c.Name AS Name,
                c.ContentImageUrl AS ImageUrl,
                c.Price AS Price,
                c.BPM AS BPM,
                f.Id AS 'File.Id',
                f.Url AS 'File.Name',
                TotalCount = COUNT (c.Id) OVER()
            FROM
                dbo.Content c
            INNER JOIN 
                dbo.PlayListContents pm ON c.Id = pm.ContentId 
                                        AND pm.PlaylistId = pl.Id
            INNER JOIN
                dbo.Files f ON c.ContentFileId = f.Id
            FOR JSON PATH
    )
) v
WHERE
    (pl.Name LIKE '%' + @searchInput + '%')
    AND v.test IS NOT NULL;

Upvotes: 0

Sowmyadhar Gourishetty
Sowmyadhar Gourishetty

Reputation: 1878

Try this

SELECT * FROM 
(Select pl.Id as Id
        ,pl.Name as Name
        ,f.[Url] as PrimaryImageUrl
        ,up.Id as MemberId
        ,up.ProfessionalName
        ,up.AvatarUrl
        ,test = ( select c.Id as Id
                    ,c.Name as Name
                    ,c.ContentImageUrl as ImageUrl
                    ,c.Price as Price
                    ,c.BPM  as BPM
                    ,f.Id as 'File.Id'
                    ,f.Url as 'File.Name'
                    ,TotalCount = count(c.Id)Over()
                            from dbo.Content c
                        inner join dbo.PlayListContents pm on c.Id = pm.ContentId and pm.PlaylistId = pl.Id
                        inner join dbo.Files f on c.ContentFileId = f.Id
                    for json path)      

        --,TotalCount = COUNT(1) OVER()
        from dbo.Playlist pl
        inner join dbo.UserProfiles up on pl.UserId = up.UserId
        inner join [dbo].[Files] as f ON pl.[PrimaryImageId] = f.[Id]
        
        
        where ( pl.Name LIKE '%' + @searchInput + '%')) a
        WHERE a.test IS NOT NULL

Upvotes: 1

Related Questions