SQL Expression Fields to retrieve data from Pervasive.SQL database

[CarrahaG]

Hi,

I am trying to increase the performance of a Crystal Report that uses ODBC to connect to a Pervasive.SQL database. Is it possible to use SQL Expression Fields in this scenario? I am trying to find some documentation on if this is possible and how it is done but can not find anything. Whatever, I try to do produces errors.

 

[lbass]

Do you see "SQL Expression" as an option in the field explorer? What version of Crystal are you using? What did you try so far?

-LB

 

[CarrahaG]

Hello LB

Yes we do see SQL Expression as an option in the Field Explorer. We are using Crystal Reports 11.

Regards,

 

[CarrahaG]

BTW,

In regards to what I have tried before, the answer is not much since I really do not have any documentation on how to work with use the SQL Epxressions Field with a Pervasive.SQL database.

 

[lbass]

What is it you want to do with the SQL expression?

-LB

 

[CarrahaG]

My report is taking too long and I want to use a SQL Expression so that the server will only pass the records needed for my crystal report.

The problem is that I am not sure if SQL Expressions are designed for reports that are not accessing SQL databases directly. We need to access a Pervasive.SQL database using ODBC.

We have two tables in a database SA_HDR and SA_LIN_ITM.

The two files are linked by the fields
SA_HDR.TICKETNO
SA_LIN_ITM.TICKETNO

We want to use a SQL Expression to retrieve only the records that meet the criteria SA_HDR.TICKETDT = '10/01/2010'. The resulting fields in the records we want passed to the report are:

SA_HDR.TICKETNO
SA_HDR.TICKETDT
SA_LIN_ITEM.ITEM_NO
SA_LIN_ITEM.QTY
SA_LIN_ITEM.PRICE

We then will use regular crystal report functions to work on the returned fields on the client side.

Regards,

 

[lbass]

You should get the same performance just by adding that criterion in your record selection formula. How are you currently adding the criterion?

If you paste in your current record selection formula and your current "Show SQL Query", we could try to troubleshoot this. You might also want to read the following thread149-1276307 .

-LB

 

[CarrahaG]

Hi LB

My report works. I just read about SQL Expressions and wanted to know if I could improve my report performance by using them. The concept of using SQL statements so that the server is able to handle some of the processing by reducing the number of records that the client works with sounds great. However, there was no document that says it is possible when working with an ODBC connection accessing a Pervasive.SQL database.

One of my questions is it possible to use SQL Expressions when there is an ODBC connection accessing a Pervasive.SQL database? If the answer is no, then I would just end it there. If it is yes, I would surely like to investigate further how this would be done and to see if I can get better performance from my report.

Regards,


Regards,

 

[lbass]

I wasn't suggesting that it didn't work--only that the date criterion should pass to the SQL even without a SQL expression.

In order to tell whether you have optimized performance, you have to make sure that all criteria that are in your selection formula are actually passing to the SQL, which is why I suggested that you post both of these in the thread.

If you are connected via ODBC to the Pervasive database, and you see SQL expression as an option in the field explorer, then yes it should be possible. You would benefit from replacing any nested formulas used in the record selection formula with SQL expressions, e.g., anything that converts a field, e.g., Ucase().

-LB