Date parameter problems

1

I have a card which uses a FromDate and a TillDate parameter. The fieldtypes are setup as Field Filter and mapped to a date field in my database. The card uses a stored procedure in my MSSQL database. When i test the card it tells me “Incorrect syntax near ‘dbo’.” When i replace the parameters with a fixed string say “20200101” And “20201231” it works as expected.

I already tried to set the field to “No special type” AND a datetype in the datamodel. But no success.

What is wrong?

2

Hi @wotikar
When using Field Filter, make sure that the query is written correctly:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
Post your query if you’re unsure.

3

Could it be a problem with the date field? The logs tells me that the date fields in the database aren’t recognized?

metabase.driver.sql-jdbc.sync Don’t know how to map column type ‘T_Date’ to a Field base_type, falling back to :type/*.

4

This is my query BTW

Exec [dbo].[MBP_Get_EmployeeHour]

@FromDate = {{FromDate}},
	@TillDate ={{TillDate}}
5

@wotikar It’s because Field Filters are special - Metabase will generate the SQL needed, when inserting them.
You cannot use Field Filters on tables with aliases (nor on functions, like you’re trying to do).
In your example, you should just use simple Date filter instead.

6

Old question I know, but when I use a SQL query with a parametter then I receive the error:

Incorrect syntax near the keyword ‘IN’.

My Query =
SELECT Dm.OrdNr
, DM.Description
, PH.ProdHeaderDossierCode
, PH.ProdHeaderOrdNr
, PBOO.ProdBOOLineNr
, PBOO.FinishedInd
, D.DeptCode
, D.Description
, PBOO.MachGrpCode
, MG.Description
, MG.DeptCode
, CONVERT(NVARCHAR(10), PBOO.StartDate, 105) AS StartDate
, CONVERT(NVARCHAR(10), PBOO.EndDate, 105) AS EndDate
, CONVERT(NVARCHAR(10), PBOO.FinishedDate, 105) AS FinishedDate
–, CONVERT(NUMERIC(6, 2), PBOO.MachSetupTime / 3600) AS MachSetupTime
–, CONVERT(NUMERIC(6, 2), PBOO.MachCycleTime / 3600) AS MachCycleTime
–, CONVERT(NUMERIC(6, 2), PBOO.MachSetoffTime / 3600) AS MachSetOffTime
, ISNULL(CONVERT(NUMERIC(8, 2), (PBOO.MachSetupTime + PBOO.MachCycleTime + PBOO.MachSetoffTime) / 3600), 0) AS VCTotalMachTime
, ISNULL(CONVERT(NUMERIC(8, 2), (
SELECT SUM(CalculatedTotalTime) / 3600
FROM T_ProcessedHour
WHERE ProdHeaderDossierCode = PH.ProdHeaderDossierCode
AND ProdBOOLineNr = PBOO.ProdBOOLineNr)), 0) AS NCMachTotalTime
FROM T_ProductionHeader PH
INNER JOIN T_ProdBillOfOper PBOO ON PH.ProdHeaderDossierCode = PBOO.ProdHeaderDossierCode
INNER JOIN T_MachGrp MG ON PBOO.MachGrpCode = MG.MachGrpCode
INNER JOIN T_Department D ON MG.DeptCode = D.DeptCode
LEFT JOIN T_ProdHeadDosDetLink PHDDL ON PH.ProdHeaderDossierCode = PHDDL.ProdHeaderDossierCode
LEFT JOIN T_DossierMain DM ON PHDDl.DossierCode = DM.DossierCode
WHERE MG.DeptCode = {{FromDeptCode}}

7

@wotikar Like I already pointed out, make sure you use the correct syntax, when using Field Filter:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

  1. You should use WHERE {{FromDeptCode}}
  2. You cannot use table aliases for the variable you’re using as Field Filter.
8

Thanks,

This =]> * You should use WHERE {{FromDeptCode}} <[= was the solution for me