CODEWITHSUNDEEP
sqlsql-servert-sql
user2822523
user2822523

Reputation: 25

Selecting From an Dynamically Created Inline Table

Ok, I have fairly large query to execute, but the requirement I have from it is simple. I need to run a Select Query with a Where clause on the Inline Table I have created in the Query itself.

Here is the code

SELECT *FROM (
    SELECT * FROM
    (
        SELECT MomentId, Moment, ExpressionsCount, ShareComments, SharedDt, SharedBy, SharedByProfilePicture, OwnerProfilePicture, SharedVia,
        MomentOwnerName, SharedById, OwnerId, SharedViaId, ROW_NUMBER() OVER (ORDER BY SharedDt DESC) As RowNum FROM
        (
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        MomentDt As SharedDt,
        '' As ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        '' As SharedByProfilePicture,
        OwnerProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        '' As SharedById,
        moments.UserId As OwnerId,
        '' As SharedViaId,
        '' As SharedVia,
        '' As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(moments.UserId, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = moments.UserId
        WHERE moments.UserId = @FriendId
        UNION ALL
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        SharedDt,
        sharing.ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        SharedByProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        OwnerProfilePicture =
        (
        SELECT
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END
        FROM tbl_User_Profiles WHERE UserId = moments.UserId
        ), 
        CAST(sharing.SharedBy As VARCHAR(40)) As SharedById,
        moments.UserId As OwnerId, CAST(sharing.SharedVia As VARCHAR(40)) As SharedViaId,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = SharedVia) As SharedVia,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = moments.UserId) As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN moments_Sharing sharing ON sharing.MomentId = moments.MomentId
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = sharing.SharedBy
        WHERE sharing.SharedBy = @FriendId
        --AND dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) = 1 AND sharing.SharedBy = @FriendId
        ) Moments
        WHERE SharingStatus = 1
    )  FinalRes
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    --ORDER BY SharedDt DESC
    )  UltRes

    WHERE RowNum BETWEEN (SELECT RowNum FROM UltRes Where MomentId = 31928) AND CASE WHEN RowNum <=5 THEN (RowNum + 5) ELSE (RowNum - 5)
END

When I run this query, it's telling me that the Object is 'UltRes' is invalid. I need the RowNum to be between the RowNum of the MomentId and based on its value, its other end is determined.

Basically I need to re-use UltRes in the Select Statement in the Where clause.

Upvotes: 1

Views: 166

Answers (2)

The Hill Boy
The Hill Boy

Reputation: 162

;WITH UltRes 

(
MomentId, 
Moment, 
ExpressionsCount, 
ShareComments, 
SharedDt, 
SharedBy, 
SharedByProfilePicture, 
OwnerProfilePicture, 
SharedVia,
MomentOwnerName, 
SharedById, 
OwnerId, 
SharedViaId, 
RowNum
)
AS(
    SELECT * FROM
    (
        SELECT MomentId, Moment, ExpressionsCount, ShareComments, SharedDt, SharedBy, SharedByProfilePicture, OwnerProfilePicture, SharedVia,
        MomentOwnerName, SharedById, OwnerId, SharedViaId, ROW_NUMBER() OVER (ORDER BY SharedDt DESC) As RowNum FROM
        (
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        MomentDt As SharedDt,
        '' As ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        '' As SharedByProfilePicture,
        OwnerProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        '' As SharedById,
        moments.UserId As OwnerId,
        '' As SharedViaId,
        '' As SharedVia,
        '' As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(moments.UserId, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = moments.UserId
        WHERE moments.UserId = @FriendId
        UNION ALL
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        SharedDt,
        sharing.ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        SharedByProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        OwnerProfilePicture =
        (
        SELECT
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END
        FROM tbl_User_Profiles WHERE UserId = moments.UserId
        ), 
        CAST(sharing.SharedBy As VARCHAR(40)) As SharedById,
        moments.UserId As OwnerId, CAST(sharing.SharedVia As VARCHAR(40)) As SharedViaId,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = SharedVia) As SharedVia,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = moments.UserId) As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN moments_Sharing sharing ON sharing.MomentId = moments.MomentId
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = sharing.SharedBy
        WHERE sharing.SharedBy = @FriendId
        --AND dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) = 1 AND sharing.SharedBy = @FriendId
        ) Moments
        WHERE SharingStatus = 1
    )  FinalRes
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    --ORDER BY SharedDt DESC
    )

   SELECT 
        *  -- SELECT SPECIFIC COLUMNS WHICH ON YOU NEED. 
   FROM 
    UltRes 
   WHERE 
    RowNum BETWEEN 
        (
            SELECT RowNum FROM UltRes Where MomentId = 31928
        ) 
    AND 
        CASE WHEN RowNum <=5 THEN (RowNum + 5) ELSE (RowNum - 5)END

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

You may use a CTE for declaring UltRes and then you may query it as if it was a table.

;WITH UltRes AS
(
    SELECT * FROM
    (
        SELECT MomentId, Moment, ExpressionsCount, ShareComments, SharedDt, SharedBy, SharedByProfilePicture, OwnerProfilePicture, SharedVia,
        MomentOwnerName, SharedById, OwnerId, SharedViaId, ROW_NUMBER() OVER (ORDER BY SharedDt DESC) As RowNum FROM
        (
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        MomentDt As SharedDt,
        '' As ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        '' As SharedByProfilePicture,
        OwnerProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        '' As SharedById,
        moments.UserId As OwnerId,
        '' As SharedViaId,
        '' As SharedVia,
        '' As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(moments.UserId, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = moments.UserId
        WHERE moments.UserId = @FriendId
        UNION ALL
        SELECT moments.MomentId,
        Moment,
        ExpressionsCount =
                (SELECT COUNT(ExpressionId) FROM moments_Expressions WHERE moments_Expressions.momentId = moments.momentId),
        SharedDt,
        sharing.ShareComments,
        FirstName + ' ' + LastName As SharedBy,
        SharedByProfilePicture =
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END,
        OwnerProfilePicture =
        (
        SELECT
        CASE
            WHEN ProfilePicture = ''
                THEN dbo.fn_GetProfilePicture(1)
            ELSE
                dbo.fn_GetProfilePicture(0) + ProfilePicture
        END
        FROM tbl_User_Profiles WHERE UserId = moments.UserId
        ), 
        CAST(sharing.SharedBy As VARCHAR(40)) As SharedById,
        moments.UserId As OwnerId, CAST(sharing.SharedVia As VARCHAR(40)) As SharedViaId,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = SharedVia) As SharedVia,
        (SELECT FirstName + ' ' + LastName FROM tbl_User_Profiles WHERE UserId = moments.UserId) As MomentOwnerName,
        dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) As SharingStatus
        FROM moments_Master moments
        INNER JOIN moments_Sharing sharing ON sharing.MomentId = moments.MomentId
        INNER JOIN tbl_User_Profiles profiles ON profiles.UserId = sharing.SharedBy
        WHERE sharing.SharedBy = @FriendId
        --AND dbo.fn_CheckUserMomentPrivacy(SharedBy, @UserId, moments.MomentId) = 1 AND sharing.SharedBy = @FriendId
        ) Moments
        WHERE SharingStatus = 1
    )  FinalRes
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    --ORDER BY SharedDt DESC
    ) 
)

SELECT * 
FROM UltRes 
WHERE RowNum BETWEEN (SELECT RowNum FROM UltRes Where MomentId = 31928) AND CASE WHEN RowNum <=5 THEN (RowNum + 5) ELSE (RowNum - 5) END

Upvotes: 1

Related Questions