OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » syntax error (disc 10.1.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

syntax error [message #356398]

Thu, 30 October 2008 11:10

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

Report message to a moderator

Re: syntax error [message #356434 is a reply to message #356398]

Thu, 30 October 2008 13:15

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: syntax error [message #356442 is a reply to message #356434]

Thu, 30 October 2008 13:36

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

Report message to a moderator

Re: syntax error [message #356448 is a reply to message #356442]

Thu, 30 October 2008 13:50

Littlefoot
Messages: 21823
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))?

Report message to a moderator

Re: syntax error [message #356449 is a reply to message #356448]

Thu, 30 October 2008 13:56

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))?

Report message to a moderator

Re: syntax error [message #356464 is a reply to message #356449]

Thu, 30 October 2008 16:09

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	image problem in reports
Next Topic:	sql query in formula column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 08:07:19 CST 2024