Getting "The column index is out of range" error when adding WHERE clause with Text Variable

This query below works in Metabase as-is (without text variable in WHERE clause)
But changing the clause
where customer ='Ford'
to
[[where customer = {{customer_var}}]]
and setting customer_var = Ford

returns this error...
The column index is out of range: 1, number of columns: 0.

How can this happen?


select *
from crosstab (
$$ select array[component, version]::text[], customer, master_local_family, local_family, int_month, SUM(component_requirement)
from tbl_monthly_ctb_demand
where customer = 'Ford'
group by component, version, customer, master_local_family, local_family, int_month
ORDER BY 1 $$,
$$ values ('202001'),('202002'),('202003'),('202004'),('202005'),('202006'),('202007'),('202008'),('202009'),('202010'),('202011'),('202012'),
('202101'),('202102'),('202103'),('202104'),('202105'),('202106'),('202107'),('202108'),('202109'),('202110'),('202111'),('202112'),
('202201'),('202202'),('202203'),('202204'),('202205'),('202206'),('202207'),('202208'),('202209'),('202210'),('202211'),('202212') $$)
AS ct(
row_id text[],
customer text,
master_local_family text,
local_family text,
Jan20 numeric(12,3),
Feb20 numeric(12,3),
Mar20 numeric(12,3),
Apr20 numeric(12,3),
May20 numeric(12,3),
Jun20 numeric(12,3),
Jul20 numeric(12,3),
Aug20 numeric(12,3),
Sep20 numeric(12,3),
Oct20 numeric(12,3),
Nov20 numeric(12,3),
Dec20 numeric(12,3),
Jan21 numeric(12,3),
Feb21 numeric(12,3),
Mar21 numeric(12,3),
Apr21 numeric(12,3),
May21 numeric(12,3),
Jun21 numeric(12,3),
Jul21 numeric(12,3),
Aug21 numeric(12,3),
Sep21 numeric(12,3),
Oct21 numeric(12,3),
Nov21 numeric(12,3),
Dec21 numeric(12,3),
Jan22 numeric(12,3),
Feb22 numeric(12,3),
Mar22 numeric(12,3),
Apr22 numeric(12,3),
May22 numeric(12,3),
Jun22 numeric(12,3),
Jul22 numeric(12,3),
Aug22 numeric(12,3),
Sep22 numeric(12,3),
Oct22 numeric(12,3),
Nov22 numeric(12,3),
Dec22 numeric(12,3)
);

1 Like

Hi @nprestel
Have a look here: Cohorts chart

Thanks @flamber...the link you sent was helpful! But still having issue :frowning:
I was able to remove all the dollar sign escapes from my query to resolve the collision with the interpreted $ variables. However, still running into the variable nested within a single-quoted query.
Besides running as a function, is there a way to escape the Metabase variable?
I have tried wrapping it with chr(36) with no luck, i.e. concat(chr(36),{{customer_var}},chr(36))

Below is my updated query which fails.

select *
from crosstab (
'Select array[component, version]::text[], customer, master_local_family, local_family, int_month, SUM(component_requirement)
from tbl_monthly_ctb_demand
WHERE {{customer_var}}
group by component, version, customer, master_local_family, local_family, int_month
ORDER BY 1',
'values (''202001''),(''202002''),(''202003''),(''202004''),(''202005''),(''202006''),(''202007''),(''202008''),(''202009''),(''202010''),(''202011''),(''202012''),(''202101''),(''202102''),(''202103''),(''202104''),(''202105''),(''202106''),(''202107''),(''202108''),(''202109''),(''202110''),(''202111''),(''202112''),(''202201''),(''202202''),(''202203''),(''202204''),(''202205''),(''202206''),(''202207''),(''202208''),(''202209''),(''202210''),(''202211''),(''202212'')')
AS ct(
row_id text[],
customer text,
master_local_family text,
local_family text,
Jan20 numeric(12,3),
Feb20 numeric(12,3),
Mar20 numeric(12,3),
Apr20 numeric(12,3),
May20 numeric(12,3),
Jun20 numeric(12,3),
Jul20 numeric(12,3),
Aug20 numeric(12,3),
Sep20 numeric(12,3),
Oct20 numeric(12,3),
Nov20 numeric(12,3),
Dec20 numeric(12,3),
Jan21 numeric(12,3),
Feb21 numeric(12,3),
Mar21 numeric(12,3),
Apr21 numeric(12,3),
May21 numeric(12,3),
Jun21 numeric(12,3),
Jul21 numeric(12,3),
Aug21 numeric(12,3),
Sep21 numeric(12,3),
Oct21 numeric(12,3),
Nov21 numeric(12,3),
Dec21 numeric(12,3),
Jan22 numeric(12,3),
Feb22 numeric(12,3),
Mar22 numeric(12,3),
Apr22 numeric(12,3),
May22 numeric(12,3),
Jun22 numeric(12,3),
Jul22 numeric(12,3),
Aug22 numeric(12,3),
Sep22 numeric(12,3),
Oct22 numeric(12,3),
Nov22 numeric(12,3),
Dec22 numeric(12,3)
);

@nprestel Metabase inserts variables with quotes, so I'm guessing that might be the problem.

Post "Diagnostic Info" from Admin > Troubleshooting, and which database type you're querying.

Here you go @flamber. I'm using PostgreSQL

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.219-164.354.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"postgres"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.9"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-06-15",
"tag": "v1.39.4",
"branch": "release-x.39.x",
"hash": "f538050"
},
"settings": {
"report-timezone": null
}
}
}

@nprestel The problem is that Metabase inserts parameters to replace the variable. But there should be a difference between a Text filter and a Field Filter. It looks like you're now using a Field Filter.
What if you do something like this?
crosstab( concat('select ...', {{variable}}, 'group ...')

This is getting into hacky territory.

The CONCAT solution is totally hacky but totally worked. Thanks for the suggestion!
Below is the code I ended up using for Text Variable + Wildcard search...

select *
from crosstab (
concat(
'select array[component, version]::text[], customer, master_local_family, local_family, int_month, SUM(component_requirement)
from tbl_monthly_ctb_demand
[[WHERE UPPER(customer) LIKE UPPER( ', '''%',{{customer_var}},'%''',
')]] group by component, version, int_month, customer, master_local_family, local_family
ORDER BY 1'),
'values (''202001''),(''202002''),(''202003''),(''202004''),
(''202005''),(''202006''),(''202007''),(''202008''),(''202009''),
(''202010''),(''202011''),(''202012''),(''202101''),(''202102''),(''202103'')
,(''202104''),(''202105''),(''202106''),(''202107''),(''202108''),(''202109'')
,(''202110''),(''202111''),(''202112''),(''202201''),(''202202''),(''202203'')
,(''202204''),(''202205''),(''202206''),(''202207''),(''202208''),(''202209'')
,(''202210''),(''202211''),(''202212'')')
AS ct(
row_id text[],
customer text,
master_local_family text,
local_family text,
Jan20 numeric(12,3),
Feb20 numeric(12,3),
Mar20 numeric(12,3),
Apr20 numeric(12,3),
May20 numeric(12,3),
Jun20 numeric(12,3),
Jul20 numeric(12,3),
Aug20 numeric(12,3),
Sep20 numeric(12,3),
Oct20 numeric(12,3),
Nov20 numeric(12,3),
Dec20 numeric(12,3),
Jan21 numeric(12,3),
Feb21 numeric(12,3),
Mar21 numeric(12,3),
Apr21 numeric(12,3),
May21 numeric(12,3),
Jun21 numeric(12,3),
Jul21 numeric(12,3),
Aug21 numeric(12,3),
Sep21 numeric(12,3),
Oct21 numeric(12,3),
Nov21 numeric(12,3),
Dec21 numeric(12,3),
Jan22 numeric(12,3),
Feb22 numeric(12,3),
Mar22 numeric(12,3),
Apr22 numeric(12,3),
May22 numeric(12,3),
Jun22 numeric(12,3),
Jul22 numeric(12,3),
Aug22 numeric(12,3),
Sep22 numeric(12,3),
Oct22 numeric(12,3),
Nov22 numeric(12,3),
Dec22 numeric(12,3)
);

1 Like

@nprestel Great that you shared how you did. I have created an issue for this:
https://github.com/metabase/metabase/issues/16949 - upvote by clicking :+1: on the first post