| Effect Verification Time | Product Version | JAR Package | Plugin Version | App Version |
|---|---|---|---|---|
| 2023/02 | FR10.0/11.0 | / | / | / |
If this document is helpful to you, you can click Helpful at the bottom, which can help us identify the content that is truly required. Your comments are also welcome if you find any problem or missing content in the document.
Problem Description
The data connection is successfully established, but an error occurs when the SQL statement is executed in the dataset.
Generally, this problem can be divided into the following two categories.
1. The SQL statement is incomplete or contains one or more syntax errors.
2. The SQL statement can be executed in the SQL editor, but it triggers an error when executed in the dataset.
Troubleshooting Steps
Incomplete SQL Statement or Syntax Error
Common Scenarios
Common errors in the SQL statement include:
| Error Type | Details |
| Table name error | Table XXX does not exist. If this error occurs, check if the name of Table XXX is input incorrectly, and then check if this table exists in the database. |
Column name error | Unknown column XX in the field list.
If this error occurs, check if the name of Column XX is input incorrectly, and then check if this field exists in the table. |
Group by error | Expression not in GROUP BY key XXX. |
Permission error | Ensure that you have the necessary permissions when querying data. Apply for permission in advance if you lack the query permission for a field or a table. Otherwise, an error similar to “You have no privilege XXX” may occur. |
Comma error | There is an extra comma, such as "select field, field,, field". One comma is missing, such as "select field, field field". |
Parentheses error | The parentheses that are nested in an SQL statement are prone to be incorrectly written. For example, if an SQL statement contains ${if}, it's easy to omit or add extra parentheses when concatenating. Check the statement carefully after input. |
Data association error | Pay attention to whether it is a one-to-one mapping or a one-to-many mapping when associating, to avoid the occurrence of Cartesian product. |
Function error | A function requires two parameters, but only one has been input. This usually happens to the date function to_date. |
Cases
1. IF Syntax Issue
Problem description: The Oracle database prompts an unsupported SQL92 token.
Solution: The Oracle statement is incorrectly written. The parameter in ${if(...)} is added with ${}, which should be removed.
2. An Extra Quotation Mark or Extra Parenthesis
a. Problem description: The SQL statement runs without error on the Oracle database, but triggers an error in the designer. The logic of the IF statement is correct, but the error "SQL command error" occurs during the preview.
Solution: It is found that there is an additional quotation mark in the statement, and the problem is solved after this quotation mark is removed.
b. Problem description: SQL statement runs normally in the Oracle database, but an error "FROM keyword not found where expected" occurs when it runs in the template.
Solution: It is found that there is an extra parenthesis in the SQL statement, and the problem is solved after it is removed.
3. AND Reuse
Problem description: ${if(len(process) == 0,"","and t1.processno = '" +process + "'")} is used and an error occurs during data preview, indicating a syntax error near "AND".
Solution: There is already an "AND" before the IF statement, so the "AND" inside the IF statement needs to be removed.
4. Special Characters in the Field Name
Problem description: An error occurs, saying "Dataset configuration error. Unknown column 'work.0.work' in 'where clause'."
Solution: Use field aliases when fetching data for fields with special characters in the name.
Abnormal Execution in Dataset but Normal in SQL Editor
Common Scenarios
The SQL statement can be executed in the SQL editor, but it triggers an error when executed in the dataset.
Common scenarios include:
1. Missing escape characters. The SQL statement can be executed successfully in the database but fails to execute in the designer. It mainly results from differences in quotation mark logic between the editor and the designer, and adding escape characters can solve this issue.
2. Field issue. There is a problem with the field inquired by the customer or the field does not meet the definition. Check and correct the field.
3. Parameter issue. The date format is prone to problems and can be resolved by nesting to_date and to_char functions, such as to_date(to_char(I.IN_DATE,'yyyy/mm/dd'), 'yyyy/mm/dd').
Cases
Missing Escape Characters
Problem description: There is a regular expression and an IF judgment in the SQL statement. The customer has successfully executed it in the database, but an error occurs when it is executed in the dataset.
Solution: Check whether the dataset syntax supports regular expressions. Create an Oracle database table locally, enter the following statement in the designer, and execute it.
${if(a=1,'SELECT regexp_replace(EMPNO, \'[^1-9]\', \'\') name FROM "Larry"."EMPS"','')}
If it can be executed successfully, this issue is caused by missing escape characters.
Field Issue
Problem description: For Oracle database 11g, the SQL statement runs normally if it only contains the SELECT statement, and an error occurs when the SUBSTR() function is added after the SELECT statement, indicating "OALL8 is in an inconsistent state". Both SQL statements can be successfully executed in the database.
Solution: Delete unnecessary fields after the SELECT statement when adding SQL statements.
Mismatch Between Literal and Format String
Problem description: The statement can be executed in the database but fails in the report and an ORA-01861 error occurs.
Solution: Something is wrong with the conversion of date in the SQL statement. Use nested to_char and to_date functions, such as to_date(to_char(I.IN_DATE,'yyyy/mm/dd') ,'yyyy/mm/dd').
Unclear Field Name During a Multi-table Query
Problem description: The SQL statement works fine in the database, but triggers an error in FineBI.
Cause analysis: The same field name exists in tables queried in the SQL statement. If it cannot confirm which field to query, an error occurs indicating an ambiguous column. Avoid using unclear field names.
Previous：Mac Designer
