Crystal 9 Upgrade - SQL

[slimshades26]

I had heard that Crystal 9 would not allow you to modify the SQL statement like we do now in 8.5. One of our network guys would like us to upgrade but we use the manual modification of SQL too much to let it go. This network guy called Crystal Decisions and they told him you can modify the SQL. Could someone offer some clarity please? Thank you.

ss26

 

[IdoMillet]

Actually, one of the nice new features in CR 9.0 is the ability to use any SQL statement (a COMMAND as Crystal 9 calls that type of data source) as the source of data for your report.

This is much more flexible than editing the SQL statement in CR 8.5 and more integrated into the Crystal designer than using the "SQL Queries" as the data source in CR 8.5.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[slimshades26]

How would I join two files where the length of the two joined fields are different? I use a substring join now and it works but how do I do this without modifing the SQL? Where do the COMMANDS come in? Thanks.

For example, I use this UDC table F0005 because it's full of descriptions for codes used in JDE screens, a subreport works but labors the report run:
FROM
(("SMPLEW01"."JDFDTAFS"."F03012" F03012
INNER JOIN "SMPLEW01"."JDFDTAFS"."F0101" F0101 ON F03012."AIAN8" = F0101."ABAN8&quot
INNER JOIN "SMPLEW01"."JDFDTAFS"."F5542016" F5542016 ON F0101."ABAN8" = F5542016."SSAN8&quot
INNER JOIN "SMPLEW01"."JDFCOMFS"."F0005" F0005 ON F0101."ABAC05" = SUBSTR(F0005."DRKY",8,3)

ss26

 

[GMcNamara]

In 8.5, you could modify the SQL by selecting Show SQL Query and making modifications. In 9, you cannot modify the SQL after you select Show SQL Query.
HOWEVER, this does not mean that you can't modify the SQL of the report. What you need to do is re-create the report and instead of adding tables, add an SQL Command. Or you may not need to re-create the report, rather just replacing certain tables with an SQL Command. You manually type in the SQL and you can modify that as much as you like.