Add Field Filters with SQL Views

Get Help
1

I created a SQL View say mv_failed_collect_pwa_trxs. This view contains a field called Provider. I intend to add a Field Filter that contains list of providers which should map to the Provider field in the view. And the list of providers is to be fetched from the Table (which the view was created from) . I am getting the error below when the filter was applied. Is there a better way to use FieldFilter with View? When the datasource is from the table which the View is created from.


SELECT 
    CAST(transaction_meta.biller_name AS VARCHAR) + ' (' + CAST(transaction_meta.biller_code AS VARCHAR) + ')'  AS "biller", 
    transaction_type, transaction_final_amount/100 as "amount", transaction_ref,
    CAST(transaction_meta.account_no AS VARCHAR) AS "account_number", 
    update_timestamp as "timestamp"
FROM mv_failed_collect_pwa_trxs 
WHERE 
    1 = 1 
    [[ AND CAST(update_timestamp AS DATE) = {{transaction_date_range}}]]
    [[AND lower({{customer_ref}}) = lower(customer_ref)]]
    [[AND lower({{provider}}) = lower(provider)]]  -- the provider filter
    [[and transaction_meta.biller_name like '%' + {{biller_name}} + '%']]
    [[and transaction_meta.biller_code = ''+{{biller_code}} +'']]
    [[and transaction_meta.account_no = ''+{{account_no}} +'']]

image
image870×171 4.23 KB

2

That query and that error message don't match up, unless your database admin has been messing with rules, and they should not ever touch those.

If the error message compl^W whined about transaction_meta, that would make sense, since you reference columns from that table without including the table in the query. But it compl^W whines about transaction_logs and there is no reference to that table in your query.

Please verify the query and error messages are correct.

EDIT: Apart from all that, when you set the source of the field filter, it must be set to a table participating in the query. If you set it to some other table, the DBMS doesn't know how to match up the data between the two and you'll get an error like that. If you need to reference data from another table, you need to JOIN it into the query so the DBMS knows how the rows from the tables are related.

In your case, if you want to filter on provider, use mv_failed_collect_pwa_trxs as the source, or add a JOIN for the other table to the query first.

1 Like
3

Thanks @dwhitemv , this really helps, I changed the source to use the view and that resolves it. Thanks