how to make "SELECT" into "SELECT DISTINCT"

[wrbodine]

Hi,

I have a report where for some reason the query is bringing back 2 of each row (in the detail section). In SQL you can remedy this by using SELECT DISTINCT. However, when I try and add the DISTINCT keyword to the report in the "Show SQL Query" section, it doesn't stay there; the change doesn't take. The report has parameters, so that could be part of why you can't modify the query. Any ideas of how to make the SELECT statement SELECT DISCINCT, or some other way to get rid of the duplicate rows?

Thanks,
Ray

 

[lyanch]

Under database on the menubar there is a choice "select distinct". This however selects distinct for all the fields selected, not for only the fields displayed. It should probably do the trick.

Lisa

 

[synapsevampire]

If you're on an early version of CR, here's the cheat.

Copy the SQL, then add in to the where clause:

where 0 = 1

Now add at the end of the SQL UNION

and paste in your SQL that you copied.

You can now add the word distinct to the post UNION SQL, and the first batch of the SQL will return nothing because of the where 0 = 1

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cmpgeek]

ok - dumb question - is there a reason why you couldnt just use the "supress if duplicated" formatting option ?

(I am still fairly new to crystal, so if I am overlooking something that is obvious to yall please be gentle. I am mainly asking because I use that option often and my numbers still look correct.)

 

[Naith]

'Suppress if Duplicated' works on fields that repeat values, so this option only applies to a single field at a time. As such, it has no affect on a whole row.

Naith

 

[cmpgeek]

ok - I think what I have been doing is formatting each field (at the same time) to supress if dupilicated, and then gone in and formatted the detail window to suppress blank sections...

 

[wrbodine]

Thanks for all of the input:

The SQL Union idea worked, after upgrading to 8.5....

 

[synapsevampire]

You must have had a very olde version of CR, I used to use the SQL Union in CR 7.

-k

 

[wrbodine]

It was version 7, maybe I just got something wrong w/ the syntax when I tried it or something.... Thanks so much for the idea!!

 

[dgillz]

sstazer,

The problem with suppression of records is that suppressed records still evaluate in any sub or grand totalling that you do, so if you do any, you will need to create running total fields instead of performing a simple summary.

You are better off using select distinct Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[synapsevampire]

Here's an example using CR 7, in case you get hit in the head with a heavy blunt object which results in your deciding to go back to CR 7

http://www.symmetrics.net/resources/html/tipstricks1d.htm

-k

 

[cmpgeek]

dgillz,
Thank you for the tip! i will bookmark this page for future reference.