Varchar column treated as Memo in Crystal

[resdevlabs]

In my database I have an EmailAddress column declared as Varchar(256). I need to be able to parse out part of the information in this field for my report. However, Crystal is treating this as a Memo field and therefore, I can't use it in a formula. Can anyone enlighten me as to why Crystal treats this as a Memo? Is there any work arounds?

TIA!

Rob

 

[synapsevampire]

Version CR 8.5 and below will, CR 9 and above will not.

Consider posting CR and database versions when posting, that way people can supply you with a solution.

I would suggest that you use a SQL Expression (INsert->Field Object->Right Click SQL Expression and select New) to parse it on the database side.

But since I don't know what you intend to parse, nor the database used, I can't supply a solution.

In Oracle you might use:

substr(table.field,1,15)

-k

 

[resdevlabs]

CR 8.5 - SQL Server 2k

I am also very restricted by the application I'm developing for. It's a Help Desk application done in ASP. It has it's own interface for adding reports to the system and I can only base reports on db views it "knows" about. I thought about using a SQL Designer query file, because I know how to parse it that way, but the Help Desk app does not recognize that datasource as a "valid view".

So, it seems an upgrade to CR9 is the only way.

Thanks for the help.

Rob

 

[synapsevampire]

No, you don't need an upgrade...

Use a SQL Expression, the above post states how to insert one, you don't need special permissions.

I still have no idea what you want to parse, otherwise I would have typed out the solution.

Please reread my post, it states that you can do this from within Crystal, it passes it to the database for processing, perhaps that part wasn't clear.

-k

 

[resdevlabs]

You also stated "Version CR 8.5 and below will, CR 9 and above will not." That led me to believe that CR 9 will not treat a Varchar(256) column as a memo.

What I am parsing is a string of email address information that includes an identifier for the person's location ("ou=&quot. Below is the SQL I have used to parse this information and the CR SQL Expression I have tried to create. The problem with CR SQL Expression is that the Clients."Email Address" is not listed in the "Field Tree". I am assuming that this is because CR is treating this column as a Memo field. This could very well be an incorrect assumption on my part. However, the problem still remains. I get "Error compiling SQL expression: ." when I try to "check" or save the SQL Expression below.

----- CR SQL EXPRESSION -----
{fn substring(Clients."Email Address",
{fn LOCATE('ou=',Clients."Email Address&quot} + 3,
{fn LOCATE( '/', Clients."Email Address", {fn LOCATE('ou=',Clients."Email Address&quot} )} -
( {fn LOCATE('ou=',Clients."Email Address&quot} + 3 ) )}

----- SQL STATEMENT -----
----- This works in SQL Designer
select substring("Email Address",
charindex('ou=',"Email Address&quot + 3,
charindex( '/', "Email Address", charindex('ou=',"Email Address&quot ) -
( charindex('ou=',"Email Address&quot + 3 ) ) as ClientLocation
from _smdba_.clients
where "Email Address" is not null

----- SAMPLE DATA -----
MAPI:{Shaner,Sharon,FORT WORTH,Customer Service}EX:/o=AlconLabs/ou=USFTW/cn=Recipients/cn=07312shanersr

 

[synapsevampire]

Sorry for not being more clear.

Use real SQL. Not sure where you came up with Locate, it's neither SQL nor Crystal that I know of.

Use what works in the SQL Designer, coincidentally, try to avoid using the Crystal SQL Designer, it's no longer required in CR 9 and just adds another layer.

substring("Email Address",
charindex('ou=',"Email Address&quot + 3,
charindex( '/', "Email Address", charindex('ou=',"Email Address&quot ) -
( charindex('ou=',"Email Address&quot + 3 ) )

-k

 

[resdevlabs]

I tried that too. But, I get several errors about "Communication Link failure" and "error in database DLL". The LOCATE function is listed in the function list in CR 8.5. If I understand correctly, the functions available to the SQL Expression are those exposed by the ODBC drivers for the particular datasource being used. I don't know why "charindex" is not exposed. I can only use what is available in CR's function list right?

At any rate, we are really dancing around the real questions. "Email Address" is being treated by CR as a Memo field (it says so in the Field Explorer).

1) Since it is declared as Varchar(256) in the database, why is it Memo and not String?

2) What, if anything, can be done to get CR to recognize the filed as a string?

Rob

 

[synapsevampire]

Sorry if you feel that we're dancing around a topic,

Crystal treats all fields over 254 as memos.

The solution is SQL Expressions, I use them daily.

If you're getting an error, then it might be your connectivity, make sure that you use the CR supplied ODBC drivers, and if it works in the SQL Server Query Designer, it should work in a SQL Expression.

-k

 

[sayasif]

I just create a view in my database to get rid of the memo mess like
select (varchar256field,1,255), etc, etc from table where etc etc


then i just point crystal to this view instead of table.

HTH
ASIF

 

[synapsevampire]

True enough, sayasif, and a big advantage to using Views is that if you have underlying database changes, you don't need to touch the report or other applications/processes, just tweak the View and everything is functioning again.

-k

 

[resdevlabs]

Well put guys. A view is an excellent idea and one that I could easily implement. But, alas, I am constrained by the system for which I am developing reports. This particular application, Magic, has its own module for adding reports to the system. All reports are actually based on views and not application tables. However, the catch is that I can only use views that have been created by the application itself. Therefore, eventhough I can create a view and a report on that view, I cannot add it to the Magic application.

Thankfully, due to synapsevampire's persistent support and excellent ideas, I came up with a solution. I used a SQL Expression and a formula.

My biggest mistake was ignoring basic debugging principles. Namely, KISS, Keep It Simple Stupid. I kept trying to fit the whole formula into the expression. After synapsevampire's constant suggestion that a SQL Expression would actually work, I began building the expression one piece at a time. I learned a couple of very interesting points.

First, you can actually use a column in a SQL expression that is not listed in the Field Tree. Obviously most of you guys know this already. But, my "Aha!" moment arrived on Friday. Thanks for all the help.

Secondly, the Locate function is CR's name for SQL Server's charindex function. Also, the Locate function will accept 3 arguments, but will not accept functions in both the 2nd and 3rd arguments at the same time. It will accept function in one or the other, but not both. Therefore, I had to hardcode the 3rd argument, which is why I then needed a formula to get my final result.

For the curious, I have included my SQL Expression and formula below.

Thanks again for all the help!

----- SQL EXPRESSION: Location -----
{fn substring(Clients."Email Address",
{fn LOCATE('ou=',Clients."Email Address&quot} + 3,
10)}

----- FORMULA: ClientLocation -----
substring( %Location,
1,
InStr( %Location, "/" ) - 1 )