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}} +'']]