Spotfire ignores Microsoft Access wildcard character ('*') when using LIKE in Microsoft Access data source query
Article ID: KB0075484
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All versions |
Description
MS Access queries containing the '*' character doesn't work in TIBCO Spotfire Analyst whereas it work in MS Access.
Example: If we create a MS Access .accdb file having Data table [Employee training tracking] with column "Course" having string data then the query returns the data in MS Access.
------------------------------------------
SELECT [Employee training tracking].Course
FROM [Employee training tracking] Where [Employee training tracking].Course LIKE "*Adobe*"
------------------------------------------
Load the created .accdb file in TIBCO Spotfire Analyst and paste the query in the SQL statement space or select the query saved in the Access file. The query goes through but the field in Spotfire returns as empty.
NOTE: Spotfire ignore '*' wildcard character when using LIKE in Microsoft Access data source query.
Example: If we create a MS Access .accdb file having Data table [Employee training tracking] with column "Course" having string data then the query returns the data in MS Access.
------------------------------------------
SELECT [Employee training tracking].Course
FROM [Employee training tracking] Where [Employee training tracking].Course LIKE "*Adobe*"
------------------------------------------
Load the created .accdb file in TIBCO Spotfire Analyst and paste the query in the SQL statement space or select the query saved in the Access file. The query goes through but the field in Spotfire returns as empty.
NOTE: Spotfire ignore '*' wildcard character when using LIKE in Microsoft Access data source query.
Issue/Introduction
Spotfire ignores Microsoft Access wildcard character ('*') when using LIKE in Microsoft Access data source query
Environment
All Supported Operating Systems
Resolution
Spotfire uses '%' as a symbol for wild character for searching one or more characters and MS Access uses '*' as a symbol for searching one or more characters. See Reference [1] for more detail
Modify the Query to use %' as the wild character in Spotfire.
So, below is the converted query:
-------------------------------------------
SELECT [Employee training tracking].Course
FROM [Employee training tracking] Where [Employee training tracking].Course LIKE "%Adobe%"
-------------------------------------------
Modify the Query to use %' as the wild character in Spotfire.
So, below is the converted query:
-------------------------------------------
SELECT [Employee training tracking].Course
FROM [Employee training tracking] Where [Employee training tracking].Course LIKE "%Adobe%"
-------------------------------------------
Additional Information
[1] SQL - Wildcard Operators - http://www.tutorialspoint.com/sql/sql-wildcards.htm
Feedback
Yes
No
Powered by
