Using "DISTINCT" in SQL 2

[sidanshu]

HI
I just want to use te keyword DISTINCT in my SQL and make my sql like - Select Distinct col1,col2... from table1 where <cond>
i write this Sql using- Show SQL Query option from database menu.The problem is as soon as i write it and say ok and close the window after that when i open the distinct keyword is not here- gets automatically removed an dso i get wrong results(multiple records infact).I am not using anu other criteria or selection forula.What can be the reason, please help.

 

[IdoMillet]

Try to simply use the 'Select Distinct Records'
option under the Database menu?

Cheers,
- Ido ixm7@psu.edu

 

[balves]

You're the victim of one of the dumbest dialog boxes ever made. And they NEVER fix it! It must be their little inside joke on the users of Crystal Reports.

You cannot change the SELECT part of the SQL statement in the SHOW SQL Query window. As soon as you close the window, it reverts to the original SQL. It should either (a) not let you do it, or (b) give an error message.

As Ido said, the newer versions of Crystal have &quot;SELECT DISTINCT&quot; as a menu option.

However, if you have an older version of Crystal, you must do it yourself. Here's one way to do it.

Go into SHOW SQL Query again. Copy the ENTIRE SQL statement and paste it below the original SQL statement. In between the 2 identical SQL statements, put the word UNION.
Change the WHERE clause of the first original statement to WHERE 0=1 and add the word DISTINCT to the second SQL statement.

 

[sidanshu]

thanks a lot ido- it works and being new to CR i don't know yet all th eoptions of it.it was simple but much gelp to me
and balves- thank you too- i'll try your option too - just to know more.
thanks again.