Running on an MSSQL Server database. A crosstab report that once ran fine gives this error now (debug on):
“SELECT DISTINCT [PRODUCT_ID] FROM [dbo].[Sales] WHERE [PRODUCT_ID] = 1,7,9,11,12 ORDER BY [PRODUCT_ID] ASC”
…the SQL should be:
“SELECT DISTINCT [PRODUCT_ID] FROM [dbo].[Sales] WHERE [PRODUCT_ID] IN (1,7,9,11,12) ORDER BY [PRODUCT_ID] ASC”
thanks.
How did you run that SQL from PHPMaker? Please explain it in more detail for more discussion.
It’s a simple crosstab report, the strange thing is the first time it runs the SQL command is correct (from debug):
sql: SELECT DISTINCT [PRODUCT_ID] FROM [dbo].[Sales] WHERE [PRODUCT_ID] = 1 OR [PRODUCT_ID] = 7 OR [PRODUCT_ID] = 9 OR [PRODUCT_ID] = 11 OR [PRODUCT_ID] = 12 ORDER BY [PRODUCT_ID] ASCBut if you refresh that same page/report you get an error:
URL: http://localhost/reports/error
Error
C:\XAMPP\htdocs\reports\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php(185): An exception occurred while executing ‘SELECT DISTINCT [PRODUCT_ID] FROM [dbo].[Sales] WHERE [PRODUCT_ID] = 1,7,9,11,12 ORDER BY [PRODUCT_ID] ASC’: SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘,’. SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
What is the PRODUCT_ID field in the report? Is it the Column Headings field or a Extended Search field? You need to post your report setting so other users can try to reproduce.
PRODUCT_ID is an integer and a Column Heading.I just realized that Extended Search was checked with array(1,7,9,11,12) in Default Value. This is an upgraded project from PHP Report Maker 12 so a filter was applied in PRM 12 that didn’t upgrade well to PHP Maker 2021.OK, removing that “fixes” the report, BUT, I would like to apply a filter to this report on PRODUCT_ID in PHP Maker 2021… how to do that now?
You may try to enable Extended Search for the field, see the topic Field Setup in the help file.