how to edit SQL

[ajdesigns]

Hi I am working in CR 10 and I need to edit the SQL for my report, I just need to put the DISTINCT clause in, I am getting duplicates.
I can view the SQL but can't edit it .
Also the option select distinct records option in the database menu is greyed out , so I cannot do it that way.
any advice
Thanks AJD

 

[Turkbear]

Hi,
CR 10 does not allow direct editing of the Sql it creates..You can, however, copy it, edit it in a text editor and use it as a command object to base the report on..You will need to reset the fields...

A better choice would be to create a view in the database, if you can, that does the Unique Sql and use that for your report.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

You might also be able to solve it within Crystal by grouping on whatever it is that is beind dulpciated. Put the value in the group header or footer, and suppress the details.

Madawc Williams (East Anglia, UK)

 

[tbear6602]

Have you tried going to Database -> Select Distinct Records? This sometimes works.

 

[synapsevampire]

As tbear pointed out, selecting Database->Select Distinct Records is identical to adding a DISTINCT clause, if your selection if grayed out, it may mean that you're using a stored procedure, or a non SQL type of database, and possibly other reasons.

Post technical information:

Data source:
Database/connectivity
Type of data source (table, view, SP)

Many think they have duplicate4s when they experience row inflation, which simply means that one or more of the child tables in the query is returning more than 1 row.

If you only need one row, then the data source is incorrect, and as Madawc pointed out, you might group at some column(s) level to make and place the rows in the group footer or group header to make it distinct (suppress the details).

-k