Efficiency problem with memo fields 2

[CestusGW]

Here is a challenge for all (which has me stumped!)
I have two databases, each containg aprroximately 100,000 records, each accessible through ODBC (I have a DSN on server). Searching through all of the records in these databases is critical. However, many of the fields I want to search on are memo fields. My initial solution for a workaround was to make an MS access file, which generated a query that had scaled down versions of the field for searching purposes (I searched the hacked off fields, displayed the full ones). However, this means that the Access database gets all the records from the databases each time it is run.

Even limiting my Access query to get 5000 records, performance time is unacceptable. Is there any way I can work my field sizes down to a searchable size while maintaing performance and selecting only the needed records at the database server level?

 

[lyanch]

I don't have any memo fields so I can test to see if this works...

I would create a SQL expression such as convert(varchar(255), table.memofield). Then you can use the SQL expression to limit the records returned. That keeps everything on the server.

As an alternate method, you could write a stored procedure that would do the same.

Lisa

 

[rhinok]

I've had mixed results using memo fields in SQL Expressions. More specifically, I can't create or refer the SQL Expressions on a consistent basis. Alternate options are:[ol][li]Create a View or Stored Procedure in which the memo fields are parsed or truncated[/li][li]Create a Crystal Query in which the memo fields are parsed or truncated[/li][/ol]When you say 'searched on', do you mean that you'd like to use the memo fields are part of your record selection statement or that you'd like to search on the memo fields after you've returned a specific dataset?

 

[CestusGW]

I wanted to do a selection based upon the memo fields. I've tried changing my report to bypass the Access database and instead try using an SQL statement to select records on the database server. This works well except for the fact I can't pass a parameter field through the SQL statement. Also, would creating a Crystal Query give me dynamic content? Ie, would the query update itself against the database each time the report was run?

 

[rhinok]

[ol][li]All of your record selection will be written into the Crystal Query. Don't create any further parameters or selection criteria in your report because they won't be processed until the query results are returned.[/li][li]Parameters can be built into the Crystal Query, too. I believe you can apply the parameters to the memo fields without modification of the memo fields, but if you can't you can always substring the memo fields. If you haven't substringed the memo fields then you still won't be able to use them for formulas in the Crystal Report.[/li][li]Reports based on a Crystal Query will be dynamic as long as you don't save data in the query when you save and close the query. Please note, everytime you refresh a Crystal Query, the 'Save Data with Query' option is checked by default. Make sure you uncheck this option before you save the query.[/li][li]If you're going to base a report on Crystal Query then make sure you save the query to a directory that Enterprise has access to. Also, use full network path authentication when you set the location (\\servername\share\subdirectory\example.qry) instead of a mapped directory (P:\directory\subdirectory\example.qry) because your mapped directory may not exist on the server.[/li][/ol]

 

[synapsevampire]

I caution against Crystal Queries for many reasons, and the rumor is that they're going to be phased out.

Why can't you pass a parameter value in the SQL statement? It works, we may need to play with the record selection criteria to get it to pass, but it will.

I've made numerous posts about building a record selection criteria so that it will pass SQL.

A few tricks are to:

-NEVER use variables in a formula which will be referenced by the record selection criteria
-Place parens () around each section of the record selection criteria

A few specifics should quickly resolve this:

What database are you using?

Record selection criteria?

Here's an example of a multiple parameter and a single parameter that creates pass through SQL, note that I check for default values of 'All' or '' to not pass anything, otherwise we use the appropriate "in" or "=":


// Multiple entry Parameter
If {?Team}[1] <> 'All'
and
{?Team}[1] <> ''
then
({MyTable.TEAMNAME} in {?Team})

else
if {?Team}[1] = 'All'
or
{?Team}[1] = '' then
True

)


and


// Single entry Parameter
(

If {?Node} <> 'All'
and
{?Node} <> ''
then
({MyTable.NODENAME} = {?Node})

else
if {?Node} = 'All'
or
{?Node} = '' then
True

)

Note the overkill in the construction of the if's, it's not because I can't code more efficiently, it's because Crystal may not pass the SQL if constructed otherwise. If I only have a single parameter or 2, and they're of the same type, often an IIF will work, but overall, this is the safest means I've found to increase the odds of pass through SQL.

I spent several days and nights working this out because neither Crystal nor TEK-TIPS examples worked when the record selection gets large.

If you can't get the SQL to pass, send along your info and let me have a go at it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Oh, and I've had good luck with SQL Expressions against MEMO fields, if you mistype some SQL, the report will act up until you do a verify database, then just clean the SQL and try again.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[CestusGW]

My problem in getting SQL statements to pass parameters is that I can't seem to code in the parameter on my own (without using the select expert).
If I were to have manually insert this line in my SQL statement:
SELECT [fields]
FROM [db]
WHERE DB.FIELD LIKE '{?Parameter}'

The report will return no values (I believe it searches for the literal string {?Parameter} rather than the variable value). Using the select expert, I can specify a field to search on and create a like statement. The only problems are:
a) I can't get my SQL statement to be updated in a web report (rendering searching useless [included promptonrefresh=1 in querystring])
b) I can't use the select expert to specify a memo field on which to base record selection.

The latter is odd, because I can construct a static SQL statement manually which will willingly search a memo field.
WHERE DB.MEMO_FIELD LIKE '%7_'
Works just fine, only it won't accept a parameter value in the report (searches for the literal parameter as above).

I'm using a BASISplus database through ODBC. ATM I'm trying not to use any native commands for it, just the universal ones as I am unable to find any documentation for it. (Note: My current understanding of SQL is that there are universal commands for the SQL standard, and then native commands only supported by database X [like Basis]. Please correct me if I am wrong, thanks)

My record selection criteria are very simple, I have three fields (each of them a memo), and I want to do LIKE matches on any or all of them. However, I also want to include a control that will block any returns if no criteria are specified (the database is too large to have all records be returned).

When using a query, I run into this problem: the Query Designer won't allow me to search on SQL Expressions (I think). The problem is that while I can generate an expression (using varchar, thank you lyanch!!), I can select it from the Select tab, and also specify the like operator. However, when I go to use the dropdown box to specify a parameter field, none are listed. I don't know the SQL syntax for specifying my expression field directly (the SQL code generated appears not to identify the SQL expression[ sometimes it will say:
Select
field,
field,
convert(varchar(255), field)

The above has no identification associated with the convert statement])

Also, building SQL expressions in Crystal Reports throws me an error when I specify a memo field (an unkown error 55065 I think).


Wow, that's a lot of info. To summarize
a) In Crystal Query I can almost do my search on the SQL Expression fields, I just think I may have built them incorrectly.
b) In Crystal Reports I can't build SQL expressions on the memo fields.


BTW syanpsevampire, is that code placed in your selection formula or the SQL statement (I'm under the impression that it operates from the selection formula then CR modifies the SQL for you).

Thanks very much to all for the great help.

Greg

 

[lyanch]

Ah, I see the problem..

Don't bother with the select expert, go to report->edit selection formula -> record.

In there add your &quot;DB.FIELD LIKE '{?Parameter}'&quot;

Crystal will write this to the SQL with the value that you give it for the parameter each time you run the report.

Say you give the value &quot;dog&quot; for the parameter, in the SQL viewer you should see:

SELECT [fields]
FROM [db]
WHERE DB.FIELD LIKE 'dog'

If you change it to &quot;cat&quot; and look again it will be

SELECT [fields]
FROM [db]
WHERE DB.FIELD LIKE 'cat'

In your case, your DB.FIELD will probably be a SQL expression you have created using your memo field.

Lisa

 

[CestusGW]

ok, I'm almost there now. My only difficulty is that I'm getting error messages when building my SQL Expression in Crystal Reports.

My SQL expression looks like this (entire code)
convert(varchar(255), DATABASE.&quot;FIELD&quot

However, wheter the field is a memo or string field I get returned this error:
ODBC error: [IDI][BASIS ODBC-SQL Driver][OpenAPI] Message Number 55056 is not defined

I'm under the sneaking impression that this means my current SQL driver doesnt support my use of convert() or varchar(255). As I said earlier this returns the same across a memo or a string field.

Is there a different SQL command that will perform the same function, or is my expression missing a key element?

Thanks again all for the help

Greg

 

[CestusGW]

as some additional info, convert() on its own throws the same error message
varchar(255) throws the same message with the number 50002

So, there's definitely something wrong with my syntax.

 

[CestusGW]

and to top off the ways I've tried to get the SQL expressions to work, I've used the query designer and successfully saved the query from there. I get the same error message when I link my Crystal Report to the Query however. I'm unsure as to what the query designer does with the expression as every time I try to run from the Query Designer my continual friend the stack overflow error message pops up (I've tried placing all the hotfixes I can find for CR to ODBC on my system, I've just learned to limit the records I look at). So, it has to be that I'm using the wrong syntax in those expressions. Would anybody know the right syntax?

 

[lyanch]

It may be the way your ODBC handles convert. As an alternative to convert in your SQL expresion try:

To_Char(&quot;DATABASE&quot;.&quot;FIELD&quot;, 1, 255)

Lisa

 

[synapsevampire]

What database are you using, and what connectivity (CR supplied ODBC, or?)?

Try writing the SQL for this conditional column in the query tool for the database and pasting it over.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[CestusGW]

The database is a BASIS 8.2 database, and the ODBC connection is through the BASIS 8.2 driver, not the Crystal Reports one (don't think there is one). The other option for TO_CHAR didn't work either :\ I got returned same error message, different number. I'm beginning to think it's not just the commands that are being rejected, it might be some other configuration. What would be a good 'universal' sql command to try running in an expression that would be recognized at the SQL1 level (I'm having my doubts that BASIS 8.2 is SQL2 compliant)?

Copy paste from the query designer wasn't going to do anything, as I got the designer to give me the same error message as before when tryin to run the same expression through it (instead of having stack overflow).

 

[lyanch]

If you want to see if a SQL statement works at all try something like:

'it' + ' ' + 'works'

Or use a regular text field for one of the words (not memo)

If you have a number field try something like:

NumberField/1

 

[synapsevampire]

Unfortunately being ANSISQL92 compliant doesn't mean that you have to do it the same way.

Consider building a View and doing your conversion there if possible.

I just read a blurb on Basis that was somewhat informative:

http://www.basis.com/advantage/mag-v4n2/firstbbj.html

I gather that you use this as a document repository?

Learning how to create a View seems the best way to deal with this, the ODBC driver may not allow for a SQL Expression to be passed, OR it may be that they have unusual synatx, you may have to <ACK!!!> RTFM.

Sorry, I hate issuing such vile threats on Fridays

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[CestusGW]

Thanks for all your help all. As it turns out my ODBC driver would hardly do any SQL statements (concatenating two static strings worked, but not two database fields???). In the end my manager said that she'd just have the database admins code another view for me. BTW, reading the manual is always my first step ... only Crystal Reports didn't seem to address these issues, and my limited BASIS database documentation seemed to say &quot;What is this 'SQL' you speak of?&quot;
BTW, SynapseVampire - that site is for a company called Basis. The BASIS database is built by people at opentext.com.