How to sort in Oracle Apex 19.1 Interactive Grid

Question

I'm using Oracle Apex 19.1 on a 18.c platform. I have a page with two regions: 1) Interactive Grid and 2) Interactive Report I use the Interactive Grid to display a list of Producer names. When the user selects one of the names, the Interactive Report refreshes and displays the events assigned to that Producer. I use a Dynamic Action to update the Interactive Report. This all works fine, except for the list in the Interactive Grid. It seems to display names in order by the table's PRIM_KEY and I want to sort the Interactive Grid by last name. The Source Code for the IG doesn't accept an Order By clause.

Select ac.first_name || ' ' || ac.last_name PRODUCER 
From aff_contact ac, aff_contact_role acr
Where ac.prim_key = acr.contact_fkey
And acr.role = 'Producer'
;

The problem is, the column names in the IG toolbar's Actions / Data / Sort never populate. I wondered if the dual column in the Select statement was the problem, so I tested it with a simpler query for the Source:

Select last_name 
From aff_contact
;

That still didn't produce columns to select for the sort. I receive a screen like the one below:

The Column select field has nothing to pull down. That holds true for both the original and the simplified query. How can I get the IG to sort based on the LAST_NAME field? Is there another way to achieve this goal? Thanks for looking at this.

Answer 1

After further checking, it seems that a VARCHAR2 column with a width <= 99 is selectable as a Sort or Control Break. A VARCHAR2 column with a width >= 100 is NOT selectable for Sort or Control Break in an interactive grid.

To identify the fields in the report Source I used the syntax: cast( first_name as varchar2(32)) "FIRST_NAME", cast( last_name as varchar2(32)) "LAST_NAME"

to avoid the default column width.

Answer 2

To sort by column:

01. Click the Actions menu, select Data, then Sort. The Sort dialog appears.

02. In the Sort dialog: Select a column, the sort direction (Ascending or Descending), and the null sorting behavior (Default, Nulls Always Last, or Nulls Always First).
  a. Column - Select a column.
  b. Direction - Select Descending or Ascending.
  c. Nulls - Select First or Last.
03. To add another sort rule, click the Add button (+).
04. Click Save.

05. Click Report
06. Click Save

The interactive grid reloads.

Note:To ensure column sorting active. To ensure that you are login developer mode, otherwise it is not show other user