Overview
As OP has improved its SQL execution to run more efficiently, SQL queries must be tweaked to continue running. Users are noticing that they will see blue or white boxes in SQL report columns where text used to appear. You can fix the SQL code so that the text will appear as expected. If you try the steps below and still need further assistance, please contact OP Support.
Edit the SQL
- Within the Database Viewer, identify the column(s) that are affected. In the screen shot above, the MDM_PLAN and CODES columns display the boxes. The other columns (PATNO, FNAME, LNAME, and DATE1) are not affected.
- Examine the SQL code and find where the column(s) are first referenced. The two columns in the example are highlighted in red.
- For each affected column in your SQL (ex: COLUMN NAME), make the following change: column_name becomes cast(column_name as char(2000)) as column_name. In the example above, the column MDM_PLAN will be changed to cast(mdm_plan as char(2000)) as mdm_plan and the CODES column will be changed to cast(codes as char(2000)) as codes.
- Click Run. You should see text as expected in the columns that you changed. In this example, the MDM_PLAN and CODES columns should now display data.
- Click the Save SQL Query text button to save the edits you made to the SQL query.
|
Note: If you get the following error message when running the edited SQL, follow the steps below.
Raise the character limit of the cast for the affected columns in the SQL you are editing:
If you get any other message, check your work to look for any typos you may have made in your SQL edit. |
Date Format
Keep in mind that, when entering run time dates in the Database Viewer, MySQL requires a different date format than Firebird. In Firebird, while using the Database Viewer, we enter dates using the mm/dd/yy format. For example, if a manager wants to run a schedule report for the first 10 days of November 2023, she might enter these parameters at run time:
However, once the practice has converted to MySQL, dates must be entered using the yyyy-mm-dd format. Here’s the same date range entered using the required MySQL format:
If a MySQL user forgets and uses the mm/dd/yy convention, the report will behave unexpectedly. Most commonly it will return no results. In other cases, it may return results outside of the date range requested.
