VERIFIED SOLUTION i

Resolve issue where User has edited SQL but no changes are taking place in Confirm

Product Feature: Ad-hoc Reporting
 

Issue

A customer reported an issue with one of the Data Source columns in one of their Data Sources in Confirm. It concerned, as per the below example, a column name Date_Completed. 

The customer changed the SQL statement for the ‘Date_Completed’ field from

TO_CHAR(job.actual_comp_date,’DD-MON-YY’)
to
TO_CHAR(job.actual_comp_date,’YYYY/MM/DD’)

...but the results still display in the original DD-MON-YY format. 

They then added a further column (Date_Comp) using

TO_CHAR(job.actual_comp_date,’YYYY/MM/DD’)

...but the results of that column again displayed as DD-MON-YY. 
 

Cause

The customer has edited their SQL in a rich text editor (e.g. Wordpad) instead of a plain text editor (e.g. Notepad) and their single quote characters got converted to something else (e.g. apostrophes).

The difference is subtle, but if you copy the following two lines into Windows Notepad you'll be able to see the difference between them:

TO_CHAR(job.actual_comp_date,’YYYY/MM/DD’)
TO_CHAR(job.actual_comp_date,'YYYY/MM/DD')

The difference is that the following won't run in Oracle (it returns an invalid character error):

SELECT
job.job_number,
TO_CHAR(job.actual_comp_date,’YYYY/MM/DD’)
FROM
job;

But the following seemingly identical SQL will run:

SELECT
job.job_number,
TO_CHAR(job.actual_comp_date,'YYYY/MM/DD')
FROM
job;

Resolution

UPDATED: September 27, 2017
We would advise that the customer deletes and re-types all single quotes in their Data Source SQL.