Field filter not working due to Table having alias in my query

Get Help
1

I want to add a filter for a particular column, however I'm having a problem as the Table this column lives in on our database is under an alias in my query.

I'm unable to remove the alias due to the query I'm running (the table is both t1 and t2).

Unsure if there's a workaround so I can still filter by the column?

2

Hi @BethL
Try posting your query. It's difficult to image how your query is made.

3

Query is..

SELECT
t1. application_id,
t1.to AS "status",
t1.updated_at AS "most_recent_update"

FROM application_events t1

WHERE t1.updated_at = (SELECT MAX(t2.updated_at)
FROM application_events t2
WHERE t2.application_id = t1.application_id)

I then want to be able to filter on the "to AS status" column

4

@BethL Then just change FROM application_events t1 to not having an alias.

SELECT
application_id,
to AS "status",
updated_at AS "most_recent_update"

FROM application_events

WHERE updated_at = (SELECT MAX(t2.updated_at) FROM application_events t2 WHERE t2.application_id = application_events.application_id)
  AND {{fieldfilter}}
5

Unfortunately when I do this it then tells me that my "to" and "updated_at" entries are ambiguous.

I've tried adding application_events.to/application_events.updated_at instead but then get the following error:

ERROR: missing FROM-clause entry for table "aplication_events" Position: 679

6

@BethL

  1. Post "Diagnostic Info" from Admin > Troubleshooting.
  2. Post the updated query. Something is telling me that you are not posting exactly what you are inputting, or you are making typing mistakes instead of copying, since there's no aplication_events (notice the single "p"), or you actually have change the table name recently and didn't sync Metabase so it's using the old reference in the Field Filter sidebar https://www.metabase.com/docs/latest/troubleshooting-guide/sync-fingerprint-scan
7

Thank you for the help - it must have been a typo as I started again and this time it worked a charm!