Selection formula -> SQL Query

[wrzek]

Hello,

I'm running Crystal v8.5.

This is my Crystal record selection formula:

{Table_1.RECTYPE} = "H" and
{Table_1.ACCOUNTNO} = "PRODUCTS" and
{TABLE.EXT} = ToText (Year (CurrentDate)) and
{TABLE.CONTACT} = "W magazynie" and
Left ({TABLE.CONTSUPREF},7 ) = {Table_1.CONTACT} and
{Table_1.PHONE} = "kg" and
(
select {?Firma}[1]
case "<STORES>" : {KONTAKTY.KEY5} = "Store"
case "<ALL>" : {KONTAKTY.COMPANY} like "*"
default: {KONTAKTY.COMPANY} = {?Company}
) )

This is SQL from Crystal | Database | Show SQL Query:
(after parameters choosing)

SELECT
Table_1."ACCOUNTNO", Table_1."RECTYPE", (...etc...)
FROM
{ oj ("Baza"."dbo"."TABLE" Table_1 INNER JOIN
"Baza"."dbo"."TABLE" TABLE ON
Table_1."CONTSUPREF" = TABLE."ADDRESS2")
INNER JOIN "Baza"."dbo"."KONTAKTY" KONTAKTY ON
TABLE."ACCOUNTNO" = KONTAKTY."ACCOUNTNO"}
WHERE
Table_1."RECTYPE" = 'H' AND
Table_1."ACCOUNTNO" = 'PRODUCTS' AND
TABLE."EXT" = '2005' AND
TABLE."CONTACT" = 'W magazynie' AND
Table_1."PHONE" = 'kg'

As You can see, there is no
„Left ({TABLE.CONTSUPREF},7 ) = {Table_1.CONTACT}”
condition and last „select” condition in SQL Query.
Two conditions are missing! Could somebody explain me this situation ?
What should I check?

Thank You very much in advance!

Wawrzyniec Chroscicki

 

[IdoMillet]

LEFT is treated as a Crystal function that the DBMS knows nothing about, so that filtering process is delegated to Crystal after the result set is returned from the DBMS.

To ensure the filtering is pushed to the backend (resulting in faster perofmance), create an SQL Expression in Crystal that uses the equivalent of the LEFT() function in your DBMS. Then use that SQL Expression in the record selection formula.

For more info, see:

http://support.businessobjects.com/library/kbase/articles/c2016184.asp

Cheers,
- Ido


Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[wrzek]

Thank You for instant answer!

Is there any place in Crystal where i can read which functions was delegated to Crystal?
The problem is in my computer it works fine, but in different computer (the same CR, database, windows) it doesn't work... I neet to check why is so, but i don't know how to check it...

I'm running MS SQL Server 2000. Could You show me how this "SQL Expresson" should looks like ? I'm not sure how to do it... I thought SQL Server supports "left" function...