Pagination in union query of two page type

What error are you receiving?

Hello Brenden,

I am getting this error

{ "Message": "An error has occurred.", "ExceptionMessage": "[SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text '\r\nWITH AllData AS \r\n(\r\n (\r\nSELECT NodeAliasPath AS [url], C.Category AS [categoryid], 'localhost:81' AS [Domain], [thumbnail], [m3u8url], 'Video' AS [objecttype], '' AS [MainImage], C.Priority, C.recommended, [DocumentID], ISNULL(DocumentPublishFrom,DocumentCreatedWhen) AS [DocumentPublishFrom], [DocumentPublishTo], ISNULL(vf.astroid,'0') AS [astroid], '' AS [likeCount], '' AS [ViewCount], '' AS [Trending], C.VideoId AS [id], [title], ' ' AS [subtitle], [sfvtags], C.description, '' AS [DescriptionBody], [content], [mediaid], vf.ItemModifiedWhen, [ClassName], [DocumentCheckedOutVersionHistoryID]\r\nFROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN AstroHotSpot_Video AS C WITH (NOLOCK) ON V.DocumentForeignKeyValue = C.VideoID AND V.ClassName = N'AstroHotSpot.Video' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID LEFT OUTER JOIN customtable_HotSpotVideoSave as vf ON C.mediaid = vf.sfvmediaid AND vf.astroid = @vf_astroid\r\nWHERE [NodeSiteID] = @NodeSiteID AND [DocumentPublishFrom] IS NOT NULL AND [mediaid] IS NOT NULL AND [m3u8url] IS NOT NULL AND [astroid] = @astroid AND (([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @Now) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @Now)) AND [DocumentCulture] = @DocumentCulture)\r\n)\r\n\r\nUNION\r\n(\r\nSELECT NodeAliasPath AS [url], C.Categories AS [categoryid], 'localhost:81' AS [Domain], '' AS [thumbnail], '' AS [m3u8url], 'Article' AS [objecttype], REPLACE(REPLACE(C.MainImage,'~',''),'/getattachment/','http://localhost:81/getattachment/') AS [MainImage], C.Priority, C.Recommended, [DocumentID], ISNULL(DocumentPublishFrom,DocumentCreatedWhen) AS [DocumentPublishFrom], [DocumentPublishTo], ISNULL(af.astroid,'0') AS [astroid], C.FavoriteCount AS [likeCount], C.ViewCount, C.Trending, C.ArticleCMSID AS [id], ArticleTitle AS [title], ' ' AS [subtitle], C.Tags AS [sfvtags], C.Description, C.ArticleBody AS [DescriptionBody], '' AS [content], '' AS [mediaid], af.ItemModifiedWhen, [ClassName], [DocumentCheckedOutVersionHistoryID]\r\nFROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) INNER JOIN HotSpot_ArticleCMS AS C WITH (NOLOCK) ON V.DocumentForeignKeyValue = C.ArticleCMSID AND V.ClassName = N'HotSpot.ArticleCMS' LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID LEFT OUTER JOIN customtable_HotSpotArticleSave as af ON C.ArticleCMSID = af.ArticleCmsId AND af.AstroId = @af_AstroId\r\nWHERE [NodeSiteID] = @NodeSiteID AND [DocumentPublishFrom] IS NOT NULL AND [mobile] > @mobile AND [astroid] = @astroid AND (([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @Now) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @Now)) AND [DocumentCulture] = @DocumentCulture)\r\n)\r\n\r\n) \r\nSELECT , (SELECT COUNT() FROM AllData) AS [CMS_TOT] \r\nFROM AllData \r\nWHERE CMS_RN BETWEEN 3 AND 4 \r\nORDER BY CMS_RN\r\n', cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [DocumentName]) AS [CMS_RN]'.", "ExceptionType": "System.NotSupportedException", "StackTrace": " at CMS.DataEngine.SqlMacroHelper.ReplaceColumns(String queryText, String columns)\r\n at CMS.DataEngine.SqlMacroHelper.ResolveMacrosInQueryText(QueryMacros queryMacros, String queryText)\r\n at CMS.DataEngine.SqlMacroHelper.ResolveQueryMacros(QueryMacros queryMacros, String queryText)\r\n at CMS.DataEngine.QueryParameters.ResolveMacrosInternal(Boolean updateQueryText)\r\n at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query)\r\n at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal()\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDB()\r\n at CMS.DataEngine.DataQueryBase1.GetData()\r\n at CMS.DataEngine.DataQueryBase1.get_Result()\r\n at CMS.DataEngine.ObjectQueryBase2.GetResults(IDataQuery query, Int32& totalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetDataFromDB()\r\n at CMS.DocumentEngine.DocumentQueryBase2.<GetDataFromDB>b__2()\r\n at CMS.DocumentEngine.DocumentQueryProperties.GetDataInternal(IDocumentQuery query, Func1 baseGetDataMethod, Action1 setTotalRecords)\r\n at CMS.DataEngine.DataQueryBase1.GetData()\r\n at CMS.DataEngine.ObjectQueryBase2.GetData()\r\n at CMS.DocumentEngine.DocumentQueryBase2.GetData()\r\n at CMS.DataEngine.DataQueryBase1.get_Result()\r\n at HotSpotAPI.ArticlesVideosSavedController.latestlist(Int32 page, Int32 pagesize, String astroid) in E:\\Kentico9 _hotspot_new\\Kentico9 _hotspot_new\\HotSpotAPI\\ArticlesVideosSavedController.cs:line 237\r\n at HotSpotAPI.ArticlesVideosSavedController.list(Int32 page, Int32 pagesize) in E:\\Kentico9 _hotspot_new\\Kentico9 _hotspot_new\\HotSpotAPI\\ArticlesVideosSavedController.cs:line 219\r\n at lambda_method(Closure , Object , Object[] )\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ApiControllerActionInvoker.

have you tried putting the column macro into the query like it recommends? wrapping the results often helps:

select ##topn## ##columns## from (
[your query here]) as fullquery where ##where## order by ##orderby##

The error has 2 specific messages in it:

• [SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text
• cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [DocumentName]) AS [CMS_RN]'

There is also a spelling error in your code for the column name:

DataTable dt= resultQuery.OrderByDescending("DocumentPublihFrom").Page(1,50).Result.Tables[0];

Should be:

DataTable dt= resultQuery.OrderByDescending("DocumentPublishFrom").Page(1,50).Result.Tables[0];

Lastly, what code do these methods have in them and what is the input paramater value:

• getVideoQuery()
• getArticleQuery()

I'm having the same issue with Union queries and pagination.

// Below, columns is a string array of columns from ContactInfo (.GetContacts() returns this type)

var relatedContactsQuery1 = ContactInfoProvider.GetContacts()
    .Columns(columns)
    .Source(s => s.Join(new QuerySourceTable("MSP_ContactRelationship", "CR"), "MSP_Contact.ContactID", "CR.ContactRelationshipContactOneId"))
    .WhereEquals("CR.ContactRelationshipUserId", currentUserId)
    .WhereEquals("CR.ContactRelationshipContactTwoId", request.ContactId)
    .AddColumn("CR.ContactRelationshipID as RelationshipId");

var relatedContactsQuery2 = ContactInfoProvider.GetContacts()
    .Columns(columns)
    .Source(s => s.Join(new QuerySourceTable("MSP_ContactRelationship", "CR"), "MSP_Contact.ContactID", "CR.ContactRelationshipContactTwoId"))
    .WhereEquals("CR.ContactRelationshipUserId", currentUserId)
    .WhereEquals("CR.ContactRelationshipContactOneId", request.ContactId)
    .AddColumn("CR.ContactRelationshipID as RelationshipId");

query = relatedContactsQuery1
    .Union(relatedContactsQuery2)
    .Page(request.PageNumber, request.PageSize);

This results in an error very similar to the one Pritam Gupta mentioned above

Message: [SqlHelper.ReplaceColumns]: Missing ##COLUMNS## macro in the query text '
WITH AllData AS 
(
(
SELECT MSP_Contact.ContactId AS [ContactId], MSP_Contact.ContactDateCreated AS [ContactDateCreated], MSP_Contact.ContactDateModified AS [ContactDateModified], MSP_Contact.ContactFirstName AS [ContactFirstName], MSP_Contact.ContactLastName AS [ContactLastName], MSP_Contact.ContactTitle AS [ContactTitle], MSP_Contact.ContactEmail AS [ContactEmail], CR.ContactRelationshipID AS [RelationshipId]
FROM MSP_Contact INNER JOIN MSP_ContactRelationship AS CR ON [MSP_Contact].[ContactID] = [CR].[ContactRelationshipContactOneId]
WHERE CR.ContactRelationshipUserId = @CR_ContactRelationshipUserId AND CR.ContactRelationshipContactTwoId = @CR_ContactRelationshipContactTwoId
)

UNION
(
SELECT MSP_Contact.ContactId AS [ContactId], MSP_Contact.ContactDateCreated AS [ContactDateCreated], MSP_Contact.ContactDateModified AS [ContactDateModified], MSP_Contact.ContactFirstName AS [ContactFirstName], MSP_Contact.ContactLastName AS [ContactLastName], MSP_Contact.ContactTitle AS [ContactTitle], MSP_Contact.ContactEmail AS [ContactEmail], CR.ContactRelationshipID AS [RelationshipId]
FROM MSP_Contact INNER JOIN MSP_ContactRelationship AS CR ON [MSP_Contact].[ContactID] = [CR].[ContactRelationshipContactTwoId]
WHERE CR.ContactRelationshipUserId = @CR_ContactRelationshipUserId AND CR.ContactRelationshipContactOneId = @CR_ContactRelationshipContactOneId
)

) 
SELECT *, (SELECT COUNT(*) FROM AllData) AS [CMS_TOT] 
FROM AllData 
WHERE CMS_RN BETWEEN 1 AND 10 
ORDER BY CMS_RN
', cannot apply the specified columns '*, ROW_NUMBER() OVER (ORDER BY [ContactLastName]) AS [CMS_RN]'.

Either there is some clever trick to getting this to work that is different when not using Union or Kentico simply doesn't support paging over Union datasets