Usage of variable quandry

Get Help
1

Greetings Metabase support,
If you could please answer me this. I am trying to include the additional WHERE clause of Cash>0 when Cash_Only-filter is 'Y". the statement below works when the variable is set to Y, but otherwise returns no results. Is this an acceptable way to implement what i am trying to do?

[[AND UPPER({{Cash_Only_filter}}) = “Y” AND Cash > 0]]

Thanks much,
Chuck

2

Hi @crwheelr
Yes, that’s the correct usage of Optional Clauses:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#optional-clauses

3

Hi @flamber,
I thought it correct. have used vars phrases numerous times. But this one doesn't execute correctly when response is null or not "y" it should be returning a larger list since the line doesn't execute the Cash > 0 in this sqlite Where clause.

lilke i said, i get the correct list when "Y" but no list with anything else. Is this a bug then? maybe there's another way to do this with the var located in the SELECT section?

Here the WHERE code showing surround statements, and the result when var = Y

image
image833×333 18.4 KB

and when var is null
image

Chuck

4

@crwheelr

  1. Post “Diagnostic Info” from Admin > Troubleshooting.
  2. Post the full query
  3. Which filter types are you using?
  4. Check your browser developer Network-tab to see the requests, which contains response data with the actual queries being executed.
5

DIAGNOSTIC INFO
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.66 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_271-b09”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_271”,
“java.vm.name”: “Java HotSpot™ Client VM”,
“java.vm.version”: “25.271-b09”,
“os.name”: “Windows 10”,
“os.version”: “10.0”,
“user.language”: “en”,
“user.timezone”: “America/Los_Angeles”
},
“metabase-info”: {
“databases”: [
“sqlite”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.36.2”,
“date”: “2020-07-31”,
“branch”: “release-0.36.x”,
“hash”: “13f0225”
},
“settings”: {
“report-timezone”: “US/Pacific”
}
}
}

FULL QUERY
SELECT
[[case when INSTR(lower(“orders_tbl”.“customer_print_name”),lower({{cust_nm}})) > 0
then “orders_tbl”.“customer_print_name”
else null
end customer,]]

strftime('%m-%d %H:%M',"orders_tbl"."order_date") AS "Date - Time", 
"orders_tbl"."cashier_id" AS "Cashier", 
"orders_tbl"."edit_id" AS "Void Cashier ID", 
"orders_tbl"."order_number" AS "Order", 
CASE "orders_tbl"."order_type"
 WHEN 1 THEN "Delivery"
 WHEN 2 THEN "Take-out"
 ELSE "Dine-in"
END "Order type",
    "orders_tbl"."extra_text" AS "Reason", 
"orders_tbl"."ord_total" AS "Total", 
"orders_tbl"."cash_tot" - "orders_tbl"."change_amt" AS "Cash", 
"orders_tbl"."credit_card_tot" AS "CC", 
CASE "orders_tbl"."paid_in_full" When 1 THEN "Yes" ELSE "No" END "Paid", 
"orders_tbl"."customer_print_name" AS "Customer", 
SUBSTR("orders_tbl"."prnt_field1",1,60) AS "Detail"

FROM “orders_tbl”
WHERE (
“orders_tbl”.“voided_order” = 1
AND date(“orders_tbl”.“order_date”) BETWEEN date(‘now’,‘localtime’,"-{{filter_days}} days") AND date(‘now’,‘localtime’)
[[AND “orders_tbl”.“cashier_id” = {{Cashier_ID}}]]
AND “orders_tbl”.“customer_print_name”= customer

[[AND UPPER({{Cash_Only_filter}}) = “Y” AND “Cash” > 0]]

)
ORDER BY “orders_tbl”.“order_date” DESC
LIMIT 1048576

FILTER TYPES
filter_days number
cashier_id number
cust_nm text
filter_cash_only text

6

@crwheelr I guess you mean Cash_Only_filter instead of filter_cash_only.
You are returning two columns as customer - that’s not helping.
And you are using "Y" instead of 'Y', so it SQLite has to guess that you are referencing a string.
Check your browser developer Network-tab to see the requests, which contains response data with the actual queries being executed.
Latest release is 0.37.2 and you should not be using H2 as the application database.

7

@flamber, I thank you for helping with my ineptitude. I just learned today proper usage of single vs double quotes i thot them interchangeable.

thanks
Chuck