RE: synapsevampire select --> select distinct

[cmpgeek]

as i am sure you were all expecting, i am now in a position where i need the select distinct function. i have read over the posts from the original thread, and synapsevampire's coding (shown below) i believed worked for the person who started that thread...

there are times when i am working in crystal when i feel like i am working with DOS again because i know everything has to be typed in exactly or it wont work... that is where my problem is... if i recognize code i can usually pull off whatever i am going for even if i DONT understand the concept itself ( i hate doing that, but sometimes the end result is more important than my knowing how i got there...) in this case i am totally lost... i know where the "where 0=1" goes; but i dont understand why nor do i grasp anything else after that...

i am truly sorry i am such a pain sometimes; any help would be greatly appreciated...

sstatzer...

synapsevampire (Programmer) Feb 19, 2003
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

 

[synapsevampire]

What version of Crystal are you using?

This solution is for older versions of Crystal, though it will also work in the current versions, but recent versions have a Select Distinct Records under the Database pull down.

Let me better explain the method:

You have SQL under Database->Show SQL Query

Copy all of that.

Now edit the where clause to include where 1 = 0

This renders that select to doing nothing.

Now add in the text UNION ALL after the existing SQL

Now paste in the select that you copied from above.

After the word select in the pasted query, add in the word DISTINCT.

And you're not being a pain.

-k