Home » Developer & Programmer » Reports & Discoverer » syntax error (disc 10.1.2)
syntax error [message #356398] Thu, 30 October 2008 11:10 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I used the following calculation in a report and i'm not sure why i'm getting the following syntax error :
error in formula: unexpected end of formula
error:syntax error:token > at position 143.

The calculation i used was :
CASE WHEN "Active/In Process".Contract Type='Project Specific' AND "Active/In Process".Letting Status='C' THEN "Active/In Process".Letting Date>SYSDATE-365 ELSE "Active/In Process".Letting Date END


Any help is greatly appreciated.

Thanks
Re: syntax error [message #356434 is a reply to message #356398] Thu, 30 October 2008 13:15 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use Discoverer, but - is this correct?

THEN "Active/In Process".Letting Date > SYSDATE-365

Re: syntax error [message #356442 is a reply to message #356434] Thu, 30 October 2008 13:36 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Yes the condition is correct and i have used the relational operators many times with out any error.
Littlefoot wrote on Thu, 30 October 2008 13:15
I don't use Discoverer, but - is this correct?

THEN "Active/In Process".Letting Date > SYSDATE-365




thanks
Re: syntax error [message #356448 is a reply to message #356442] Thu, 30 October 2008 13:50 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you say so ...

However, it still doesn't smell good to me:
CASE 
  WHEN "Active/In Process".Contract Type = 'Project Specific' AND 
       "Active/In Process".Letting Status = 'C' 
  THEN 
       "Active/In Process".Letting Date > SYSDATE - 365        --> this results in BOOLEAN
  ELSE 
       "Active/In Process".Letting Date                        --> this is a DATE
END

So, how does the same CASE return different data types (not to mention that Boolean is an invalid datatype in SQL (but valid in PL/SQL))?
Re: syntax error [message #356449 is a reply to message #356448] Thu, 30 October 2008 13:56 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank you , but is there any work around? Do you think by using TO_NUMBER will do any good? Please correct me if i'm wrong.
Thanks
Littlefoot wrote on Thu, 30 October 2008 13:50
If you say so ...

However, it still doesn't smell good to me:
CASE 
  WHEN "Active/In Process".Contract Type = 'Project Specific' AND 
       "Active/In Process".Letting Status = 'C' 
  THEN 
       "Active/In Process".Letting Date > SYSDATE - 365        --> this results in BOOLEAN
  ELSE 
       "Active/In Process".Letting Date                        --> this is a DATE
END

So, how does the same CASE return different data types (not to mention that Boolean is an invalid datatype in SQL (but valid in PL/SQL))?

Re: syntax error [message #356464 is a reply to message #356449] Thu, 30 October 2008 16:09 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A workaround? Probably. A good start would be to present your input and explain the desired output. Because, just an invalid part of a certain statement is far from enough. At least, from my point of view.
Previous Topic: image problem in reports
Next Topic: sql query in formula column
Goto Forum:
  


Current Time: Wed Dec 07 16:39:08 CST 2016

Total time taken to generate the page: 0.24247 seconds