Query related

  

SyntaxEditor Code Snippet

SELECT  @CampaignQueryFilterString AS [Selected],
        {Deck}.[Id], {Deck}.[Name],
       
        {Deck}.[TableOfContentId], {Deck}.[CreatedBy],
        {User}.[Name],
        {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate],s1.[Count]      
        
        FROM  {Deck}
        
        
        (
            SELECT {Deck}.[Id],
            LISTAGG( {TagValue}.[Id], ',' ) WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId] ) AS Tags
            FROM {Deck} 
            JOIN {AssociatedDeckTags}
            ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id]
            JOIN {TagValue}
            ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id]
            WHERE {Deck}.[IsPublished] = 1
                    AND {Deck}.[IsActive] = 1 AND {Deck}.[ReplacedByDeckId] IS NULL
                    AND {Deck}.[TableOfContentId] IN @TableOfContentIdFilterString
                    AND {Deck}.[ContentFileTypeId] IN @AllowedContentType
                    AND {Deck}.[ExpiryDate] > SYSDATE
            GROUP BY {Deck}.[Id]   
            
        ) DeckView
        ON {Deck}.[Id] = DeckView.Id 
        
        
        JOIN(
            SELECT COUNT(*) AS [Count], {DeckGroup}.[DeckId] AS [S1DeckId]
            FROM {DeckGroup}
            JOIN {Slide} ON {Slide}.[DeckGroupId] = {DeckGroup}.[Id]
            GROUP BY {DeckGroup}.[DeckId]
        ) s1 ON s1.[S1DeckId] = {Deck}.[Id]
        
        
       
        @RegexString
        @SearchFilter
        @CreatedBy
        GROUP BY 
        {Deck}.[Id], {Deck}.[Name], {Deck}.[TableOfContentId], 
        {Deck}.[CreatedOn], {Deck}.[CreatedBy],
        {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate], 
        {Deck}.[NumOfPreviews], {Deck}.[NumOfDownloads],s1.[Count]
         
        ORDER BY @Orderby


i have to place the 

" JOIN {User}
        ON {Deck}.[CreatedBy] = {User}.[Id]" inside it. I don't understand where to place it. 

hi Lovish,

may this link helps:

https://www.w3schools.com/sql/sql_join.asp

regards,

A

Anonymous wrote:

hi Lovish,

may this link helps:

https://www.w3schools.com/sql/sql_join.asp

regards,

A


i had tried many times but unable to add " JOIN {User}
        ON {Deck}.[CreatedBy] = {User}.[Id] " in the query . Everytime i get error. So please help me out 

I Box wrote:

hi Lovish,

are they external tables?

https://www.outsystems.com/forums/discussion/26178/join-users-with-external-database/

regards,

IB


no they are not external tables

Lovish Goyal wrote:

I Box wrote:

hi Lovish,

are they external tables?

https://www.outsystems.com/forums/discussion/26178/join-users-with-external-database/

regards,

IB


no they are not external tables


actually the query is below

SyntaxEditor Code Snippet

SELECT  @CampaignQueryFilterString AS [Selected],
        {Deck}.[Id], {Deck}.[Name],
        {Deck}.[TableOfContentId], {Deck}.[CreatedBy],
        {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate],s1.[Count]      
        FROM  {Deck}
        JOIN
        (
            SELECT {Deck}.[Id],
            LISTAGG( {TagValue}.[Id], ',' ) WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId] ) AS Tags
            FROM {Deck} 
            JOIN {AssociatedDeckTags}
            ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id]
            JOIN {TagValue}
            ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id]
            WHERE {Deck}.[IsPublished] = 1
                    AND {Deck}.[IsActive] = 1 AND {Deck}.[ReplacedByDeckId] IS NULL
                    AND {Deck}.[TableOfContentId] IN @TableOfContentIdFilterString
                    AND {Deck}.[ContentFileTypeId] IN @AllowedContentType
                    AND {Deck}.[ExpiryDate] > SYSDATE
            GROUP BY {Deck}.[Id]       
        ) DeckView
        ON {Deck}.[Id] = DeckView.Id  
        JOIN(
            SELECT COUNT(*) AS [Count], {DeckGroup}.[DeckId] AS [S1DeckId]
            FROM {DeckGroup}
            JOIN {Slide} ON {Slide}.[DeckGroupId] = {DeckGroup}.[Id]
            GROUP BY {DeckGroup}.[DeckId]
        ) s1 ON s1.[S1DeckId] = {Deck}.[Id]
        @RegexString
        @SearchFilter
        @CreatedBy
        GROUP BY 
        {Deck}.[Id], {Deck}.[Name], {Deck}.[TableOfContentId], 
        {Deck}.[CreatedOn], {Deck}.[CreatedBy],
        {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate], 
        {Deck}.[NumOfPreviews], {Deck}.[NumOfDownloads],s1.[Count]        
        ORDER BY @Orderby

and i want to add 

JOIN {User}
        ON {Deck}.[CreatedBy] = {User}.[Id]