How to run a SQL query in Crystal Reports?

[amfiteatro]

Hello, I'm wondering if there is anyone who might be able to assist. I'm trying to run the following query in Crystal reports, but I don't know how to complete this code in the formula editor. Does anyone know how to create a join from the same table and pull information with the same fields as shown below? I have the report pulling information from the payments table so far, but I dont know how to do the join part. Could anyone please help?
Thank you very much!!!

select *
from payments a
join
(select debit_address1, debit_city, debit_state, debit_zip
from payments
group by debit_address1, debit_city, debit_state, debit_zip
having count(*) > 1) b
on a.debit_address1 = b.debit_address1
and
a.debit_city = b.debit_city
and
a.debit_state = b.debit_state
and
a.debit_zip = b.debit_zip

 

[vidru]

I've had some success with derived tables in Crystal, but messing with the Record Selection formula might screw this up.

This solution may be database dependent, so it may or may not work for you.

If you're using Crystal 8.0 or 8.5:
Add the Payments table to the report, and put the fields you need on the report. Then go to Database>Show SQL Query, and replace the text with this:

select *
from payments
join 
(select debit_address1, debit_city, debit_state, debit_zip  
from payments 
group by debit_address1, debit_city, debit_state, debit_zip   
having count(*) > 1) b  
on payments.debit_address1 = b.debit_address1
           and
   payments.debit_city = b.debit_city
           and
   payments.debit_state = b.debit_state
           and
   payments.debit_zip = b.debit_zip

If you're using Crystal 9 or higher:
Use a SQL Command and paste in your query.

-dave

 

[amfiteatro]

I was able to input that data, however, even though I have set a beginning date and an end date of say 12/1/03 to 12/31/03, it pulls information from 02/01/04. I'm not sure if this is query related or if i have to make any changes in the database query itself.... i'm so lost...