Hyperion Production Reporting Returns Error "(105) Unclosed quote before the character string" when Using Date Columns in Expressions and Where Clause (Doc ID 1528265.1)

Last updated on SEPTEMBER 02, 2016

Applies to:

Hyperion BI+ - Version 11.1.2.2.000 and later
Information in this document applies to any platform.

Symptoms

DATE columns used in expressions and WHERE clause cause the following error when the -XP command line flag is used.

Hyperion SQR Production Reporting Server - 11.1.2.2.0.110
Copyright (c) 1994, 2011 Oracle and/or its affiliates.  All rights reserved.

Erreur begin-select Status : 105.000000 (102) Incorrect syntax near '19870612'.
   (105) Unclosed quote before the character string '))  '.
SELECT dateadd(dd,-10,convert( 19870612 00:00:00.000'))

Below is a test program (test.sqr) to reproduce the problem against a database called pubs2.

The workaround is not to use the -XP flag or pass the dates as string variables instead of date columns. The latter is included in the sample.

 

Begin-Setup
use pubs2
End-Setup
!
Begin-program
begin-select on-error=erreursql
pubdate                             &org_date_comptable
!   let $org_date_comptable = &org_date_comptable  ! workaround
   let #org_trouve = 1
from titles
end-select
!
begin-select on-error =erreursql
dateadd(dd,-10,&org_date_comptable) &date_limite_reponse
!dateadd(dd,-10,$org_date_comptable) &date_limite_reponse  ! workaround
!   let $date_limite_reponse = &date_limite_reponse  ! workaround
end-select
!
begin-select on-error=erreursql
pubdate                             &org_date_comptable_2
   let #org_trouve = 1
from titles
where pubdate < &date_limite_reponse  
!where pubdate < $date_limite_reponse  ! workaround
end-select
End-Program
!
begin-procedure erreursql
show 'Erreur begin-select Status : '  #sql-status  ' ' $sql-error
show $sql-text
let #return-status = 111
stop  
end-procedure

 

 

 

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms