How to do a SELECT DISTINCT in Crystal 7? 4

[PTW]

I'm trying to stop identical records from appearing on my report, but the "Show SQL Query" screen does not save any changes made to the Select portion of the SQL statement.  How do I add a Distinct clause or at least simulate it?  Thanks for any help!

 

[VAS]

Choose Database and then click on Select Distinct Records to add the<br>DISTINCT clause to your SELECT statement.<br><br>V<br>

 

[ABerkowicz]

Depending on what you are exactly trying to do, one solution may be to insert a group and move all the information up to the group level, instead of having it at the detail level. Then if you hide the details section, you will only get one record for each item.<br><br>

 

[superdorm]

VAS, I use CR Ver 6.0 i cannot find Database--> Select Distinct Records, Is the menu in Ver 6.0.
How can i do, if it's not

Thanks

[sig][/sig]

 

[bgoliver]

The following is Seagate's response in there Knowledge Base. The article is C20007732




How to do a Select Distinct



The information in the article refers to:
Seagate Crystal Reports 7
Applies to:
Reported version and lower
SQL Statement
Selection Criteria


Synopsis

When creating reports off SQL/ODBC databases, how do
you print only distinct records?

Solution

Crystal Reports (CR) does not allow you to modify the first
SELECT clause. You consequently cannot add the DISTINCT
word to the initial SELECT statement to make the server
return only distinct records. However, CR does allow you to
add a second SELECT clause.

Here is a way to get CR to do a SELECT DISTINCT:

· From the 'Database' menu, select 'Show SQL'.
· Copy the entire SQL statement of the report and duplicate
it. Add a 'UNION' clause between the two SQL statements.
· Modify the WHERE clause of the first SQL to say 'WHERE
0=1'. If the SQL does not have a WHERE clause, add one.
'WHERE 0=1' makes the first SQL a dummy SQL that does
not select any records.
· Modify the second SQL to read 'SELECT DISTINCT'. Since
this is the second SELECT statement in the SQL, Crystal
Reports will allow you to modify it and add the 'DISTINCT'
word.

The final SQL should look like:

SELECT
<field list>
FROM
<table list>
WHERE
0=1
UNION
SELECT DISTINCT
<field list>
FROM
<table list>
WHERE
<record criteria>

=============
Please note: this change only works if it is the last change
made to the report. Once the change has been made, if
changes are made to the record selection or anything else
that would normally affect the SQL Query, it would need to
be re-edited.
=============


Category:
&quot;Report Designer&quot;
Subject:
&quot;Database
Connections/Operations&quot;
Topic:
&quot;SQL Statement&quot;
Keywords:
MODIFY SQL STATEMENT
DISTINCT SELECTION


Status:
Verified
Product:
Crystal Reports Professional
Reported Version:
7.0.100.15 Crystal Reports -
MR1
Applies to:
Reported version and lower
Bit Version:
16 & 32 Bit [sig][/sig]

 

[HulaGirl]

I am new to CR and I am using CR V7. I am trying to get members' names and address to be printed on mailing lables. I have defined selection criteria in the select expert. However, certain members have multiple records that fall in the range of the selection criteria. As a result Identical names and address printed out multiple times. I tried using SELECT DISTINCT and it did not help, cause they are DISTINCT except for names and addresses. How do get distinct name and address only?? Any tip or suggestion is much appreciated in advance. SQL queries are copied as follow:

SELECT
claimdetail.&quot;servcode&quot;, claimdetail.&quot;dosto&quot;,
member.&quot;dob&quot;,
entity.&quot;entname&quot;, entity.&quot;addr1&quot;, entity.&quot;addr2&quot;, entity.&quot;city&quot;, entity.&quot;state&quot;, entity.&quot;zip&quot;
FROM
{ oj (((claim INNER JOIN enrollment ON
claim.&quot;enrollid&quot; = enrollment.&quot;enrollid&quot
INNER JOIN claimdetail ON
claim.&quot;claimid&quot; = claimdetail.&quot;claimid&quot
INNER JOIN member ON
enrollment.&quot;memid&quot; = member.&quot;memid&quot
INNER JOIN entity ON
member.&quot;entityid&quot; = entity.&quot;entid&quot;}
WHERE
(claimdetail.&quot;servcode&quot; = 'Z9000' OR
claimdetail.&quot;servcode&quot; = '99395' OR
claimdetail.&quot;servcode&quot; = '99394' OR
claimdetail.&quot;servcode&quot; = '99393' OR
claimdetail.&quot;servcode&quot; = '99392' OR
claimdetail.&quot;servcode&quot; = '99391' OR
claimdetail.&quot;servcode&quot; = '99385' OR
claimdetail.&quot;servcode&quot; = '99384' OR
claimdetail.&quot;servcode&quot; = '99383' OR
claimdetail.&quot;servcode&quot; = '99382' OR
claimdetail.&quot;servcode&quot; = '99381')