Crytal SQL Formula Problems - String

[LeeW2]

I use Crystal 8 Report Writer a lot, and am familiar with the structure of formulae. However I am having trouble writing SQL expressions on Crystal SQL Writer.

I am sure it is just a syntax thing, but the Help facility does not have any examples on it.

All I want to do is to use the String functions - Left/Right/Mid etc.

I have created a new Expression to break up a 120 character string into separate sections. The formula I have used is:

Left ({PRL_REG_HEAD.USER_AREA},20)

and

Mid ({PRL_REG_HEAD.USER_AREA},21,20)

where {PRL_REG_HEAD} is the ODBC Dictionary Name, and USER_AREA is the field name.

This mirrors the formula I use in Crystal Report Writer, but returns the message 'Illegal Character'. If I change the '{' brackets to '(' brackets, then it says 'The Remaining Text Does Not Appear To Be Part of The Formula'.

Any idea where I am going wrong?

Thanks

 

[IanWaterman]

You are using Crystal Syntax which may not be compatible with your database. For example in Oracle you can not use Mid or left you have to state as
Substr(PRL_REG_HEAD.USER_AREA,21,20) for your mid and
Substr(PRL_REG_HEAD.USER_AREA,1,20) for your left

Ian Waterman
UK Crystal Consultant