Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Can I use SQL in the Table Transformer macro to filter for status macro values?

Ted Nienstedt February 15, 2024

I'm using Page Properties and Page Property Report macros to make customer reports of pages using specific Page Properties.  In one of my reports I'd like to include only Page Properties that contain a column called Status that contains a Status Macro with the value of "High".  I'm using the Table Transformer macro to provide SQL filter.  I tried the following, but didn't work: "SELECT * FROM T* WHERE 'Status' ~ "High".

I'm aware of the ability to filter based on an ID provided in Page Properties macros, but I'm looking for a finer degree of filtering beyond the simple example given above, such as with multiple columns (eg, Status combined with Importance)

2 answers

3 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 15, 2024

Hi @Ted Nienstedt ,

Please try the following SQL statement:

SELECT * FROM T* WHERE 'Status' LIKE "High"

It should be working fine.

0 votes
Ted Nienstedt February 15, 2024

I found that ::string was necessary as follows

SELECT * FROM T* WHERE 'Status'::string LIKE "High"

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 15, 2024

No, the ::string part is not necessary for the WHERE clause.

If the issue is that you don't get any errors but you just don't get any matching rows, it means that there are no such rows indeed.

You've mentioned that you used the "Status Macro with the value of "High" - try to filter your table by "HIGH" and not "High". It seems that the macro statuses are written in capital letters. 

And the tilde "~" has a kind of a similar meaning as a simple LIKE (that is often considered to be an SQL-standard), your first query should be also recognized correctly. 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events