ListColumn_sort

  

how to apply listcolumnsort on  extra coulmn evaluated attribute of an aggregate? which table name i have mention to sort that value because it is evaluated and not part of any entity

No Table name, no [ ] around the name of the calculated field.
Just use the name of the field.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

No Table name, no [ ] around the name of the calculated field.
Just use the name of the field.

Cheers,
Eduardo Jauch

not working. My evaluated attribute is Content Owner, so i am  writing in column "Content Owner" and in 

List_SortColumn_GetOrderBy(TableRecordsForDecks.Id,"ContentOwner"). "ContentOwner" is evaluated value




With a space?

Try put this way: "[Content Owner]"

Eduardo Jauch wrote:

With a space?

Try put this way: "[Content Owner]"

This is not working out, could you please share me an example. I want to apply list sort on evaluated value and i am getting this error.


Error in advanced query GetDecks in Preparation in ContentBrowsingWidget in ContentOwner in SlideAdministrator (SELECT {Deck}.[Id], {Deck}.[Name] , {Deck}.[TableOfContentId],   {Deck}.[CreatedOn], {Deck}.[CreatedBy],  {Deck}.[LastModifiedOn], {Deck}.[ExpiryDate],   {Deck}.[NumOfPreviews], {Deck}.[NumOfDownloads], 0 "IsSelected",  {Deck}.[IsPublished]  FROM  {Deck} JOIN   (  SELECT {Deck}.[Id],  LISTAGG( {TagValue}.[Id], ',' ) WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId] ) AS Tags  FROM {Deck}   LEFT JOIN {AssociatedDeckTags}  ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id]  LEFT JOIN {TagValue}  ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id]  LEFT JOIN {TagCategory}  ON {TagCategory}.[Id] = {TagValue}.[TagCategoryId]  WHERE  {Deck}.[IsActive] = 1  AND  {Deck}.[TableOfContentId] IN @TableOfContentIdFilterStri  @CreatedByFilter  @SearchFilter  GROUP BY {Deck}.[Id]   ) DeckView   ON {Deck}.[Id] = DeckView.Id  @RegexString  ORDER BY @OrderBy  ): ORA-00936: missing expression


Hello Lovish.

THis is your query (according to the message):

SELECT
    {Deck}.[Id],
    {Deck}.[Name],
    {Deck}.[TableOfContentId],
    {Deck}.[CreatedOn],
    {Deck}.[CreatedBy],
    {Deck}.[LastModifiedOn],
    {Deck}.[ExpiryDate],
    {Deck}.[NumOfPreviews],
    {Deck}.[NumOfDownloads],
    0 "IsSelected", 
    {Deck}.[IsPublished] 
FROM  {Deck}
    INNER JOIN
        (
            SELECT
                {Deck}.[Id], 
                LISTAGG( {TagValue}.[Id], ',' )
                    WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId]) AS Tags 
            FROM {Deck}  
                LEFT JOIN {AssociatedDeckTags} 
                ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id] 
                LEFT JOIN {TagValue} 
                ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id] 
                LEFT JOIN {TagCategory} 
                ON {TagCategory}.[Id] = {TagValue}.[TagCategoryId] 
            WHERE 
                {Deck}.[IsActive] = 1 
                AND 
                {Deck}.[TableOfContentId] IN @TableOfContentIdFilterStri  @CreatedByFilter  @SearchFilter 
            GROUP BY {Deck}.[Id]  
        ) DeckView  
    ON {Deck}.[Id] = DeckView.Id  @RegexString 
ORDER BY @OrderBy

I'm not too familiarized with Oracle, but I see some potential problems in your query.

First, the clause IN should be folowed by a list of values between parenthesis, separated by comma.
Something like this:

{entity}.[field] IN (value1, value2, value3)

This is highly suspicious to me:

{Deck}.[TableOfContentId] IN @TableOfContentIdFilterStri  @CreatedByFilter  @SearchFilter

Another very suspicious line is this one:

ON {Deck}.[Id] = DeckView.Id  @RegexString

Depending on what you are passing as values to these arguments, you will be violating the SQL syntax rules and will get errors like thie one you have now.

So, what are the exact values you are passing to these arguments?

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hello Lovish.

THis is your query (according to the message):

SELECT
    {Deck}.[Id],
    {Deck}.[Name],
    {Deck}.[TableOfContentId],
    {Deck}.[CreatedOn],
    {Deck}.[CreatedBy],
    {Deck}.[LastModifiedOn],
    {Deck}.[ExpiryDate],
    {Deck}.[NumOfPreviews],
    {Deck}.[NumOfDownloads],
    0 "IsSelected", 
    {Deck}.[IsPublished] 
FROM  {Deck}
    INNER JOIN
        (
            SELECT
                {Deck}.[Id], 
                LISTAGG( {TagValue}.[Id], ',' )
                    WITHIN GROUP (ORDER BY {TagValue}.[TagCategoryId]) AS Tags 
            FROM {Deck}  
                LEFT JOIN {AssociatedDeckTags} 
                ON {AssociatedDeckTags}.[DeckId] = {Deck}.[Id] 
                LEFT JOIN {TagValue} 
                ON {AssociatedDeckTags}.[TagValueId] = {TagValue}.[Id] 
                LEFT JOIN {TagCategory} 
                ON {TagCategory}.[Id] = {TagValue}.[TagCategoryId] 
            WHERE 
                {Deck}.[IsActive] = 1 
                AND 
                {Deck}.[TableOfContentId] IN @TableOfContentIdFilterStri  @CreatedByFilter  @SearchFilter 
            GROUP BY {Deck}.[Id]  
        ) DeckView  
    ON {Deck}.[Id] = DeckView.Id  @RegexString 
ORDER BY @OrderBy

I'm not too familiarized with Oracle, but I see some potential problems in your query.

First, the clause IN should be folowed by a list of values between parenthesis, separated by comma.
Something like this:

{entity}.[field] IN (value1, value2, value3)

This is highly suspicious to me:

{Deck}.[TableOfContentId] IN @TableOfContentIdFilterStri  @CreatedByFilter  @SearchFilter

Another very suspicious line is this one:

ON {Deck}.[Id] = DeckView.Id  @RegexString

Depending on what you are passing as values to these arguments, you will be violating the SQL syntax rules and will get errors like thie one you have now.

So, what are the exact values you are passing to these arguments?

Cheers,
Eduardo Jauch


In table column, values are coming from entity Deck and I can apply list sort on that columns , but there is one column name content owner , which I evaluated from server action . So how to put list sort on that evaluated expression value

I have tried everything with "ContentOwner" , "[ContentOwner]" , but nothing is working. So what's the solution

Hello Lovish,

I'm asking the exact values you are passing to the parameters: 

@TableOfContentIdFilterStri  
@CreatedByFilter  
@SearchFilter
@RegexString

and


@OrderBy


Because I really think your problem is at this point.

Also, where is that "Content Owner" column in the query you showed?
Previously you stated that it was a calculated column, but I don't see any calculated column in this query, except by the 


0 "IsSelected"


But your @OrderBy, if it is using a column that do not exist in the query, will also cause problems.

So, where is this column "Content Owner" you are mention?

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hello Lovish,

I'm asking the exact values you are passing to the parameters: 

@TableOfContentIdFilterStri  
@CreatedByFilter  
@SearchFilter
@RegexString

and


@OrderBy


Because I really think your problem is at this point.

Also, where is that "Content Owner" column in the query you showed?
Previously you stated that it was a calculated column, but I don't see any calculated column in this query, except by the 


0 "IsSelected"


But your @OrderBy, if it is using a column that do not exist in the query, will also cause problems.

So, where is this column "Content Owner" you are mention?

Cheers,
Eduardo Jauch

hi Ed, i have 6 column in table, out of which 5 are coming from single entity name deck and 1 is coming from server action . And i applied aggregate inside server action and that aggregate include evaluated value. I tried with each and every combination but not able to sort. Have you tried it in your environment? if yes then please share me the module


Hi Lovish,

it would probably be simpler if you can provide a sample module with the issue you're facing so we can better understand what you're doing there.

Hum... I think I finally understood the problem...

Lovish,

When you say that you have a six column table, are you referring a Table Records in the screen? Than wwhat you say starts to make sense.

Unfortunatelly, if one of the columns is an external column, meaning the value is dinamically defined when the Table is being build into the HTML, and is at this moment the server action is called to return the value it will be used in the cell, you have a challenge.

See, you can't order the source (query), using a field that is not in the query itself.

So, the first possible solution is to change your logic, so that instead of computing the value in the table, you do this operation in the query itself. If you can do this, than you can relay on the standard dynamic order of Richwidgets List_SortColumn.

If the logic is too complex or impossible to change into a query logic, you can try to fill the values in the source list before providing it to the Table Records. This way you have the possibility, through using the ListSort action (from System) and your own logic, to sort the list everytime you want, and refresh the Table records to reflect this.

Other than that, you could try to sort the list client side, but I think this would probably be the worst idea, in terms of maintanability. Maybe there is a Forge component that can do this, but I don't know any.

Hope this can help cast some light over the problem.

Cheers,
Eduardo Jauch