Missing Column Search

[bdisalvo]

I have a file that I am trying to display in a grid.
The file has several columns that have numeric content, but are in alpha columns.
(This is probably because many years ago this file was an after-the-fact extract from a report file.)

I have By-Column searching set on this grid.
I usually want my numeric columns to have a numeric-style <, >, = search paradigm.

So I took these numbers-in-alpha columns and wrote something like this in the data source sql select:
Select decimal(colname, 7, 2) as colname, ...

These columns do not have a Search option in their dropdowns at all, while all the other more native columns around them do.

Is this the right technique or should I do something else?
Bob

[Johnny Major]

Hi Bob,

Right now we don't support searching by column on columns that are not an actual field in the table so that is why you don't see the search option on the calculated column(s) "decimal(colname, 7, 2) as myCol;

We have added this to our list of feature requests

[Johnny Major]

Bob,

To get around this for the time being you could have your data source create a temp table then those calculated columns should be searching able via column search.

Example below allows column TSTLOC to be searchable via column search

WITH wpl(cusno, cname, ccountry, tstloc) AS (
  SELECT
    cusno,
    cname,
    ccountry,
    trim(cstate) || ', ' || trim(ccity) AS tstloc
  FROM
    democmast
)
SELECT
  wpl.*
FROM
  wpl

[Johnny Major]

I want to clarify that the search by column will not be displayed on aggregate columns "SUM, MIN, MAX, AVG, DISTINCT" and the solution is to use a temp table in the data source

WITH wpl(sales, country) AS (
  SELECT
    sum(d.ordqty * d.price) AS totalsales,
    cus.ccountry AS cus_ccountry
  FROM
    democmast AS cus
    INNER JOIN demoord_h AS h ON h.cusno = cus.cusno
    INNER JOIN demoord_d AS d ON d.orderno = h.orderno
  GROUP BY
    cus.ccountry
)
SELECT
  wpl.*
FROM
  wpl