Crystal reports not pulling same data as sql server

Question

I have the following code:

select order_number,received_date
from order_head
where order_head.order_number not in (select order_number from csa_log group by order_number)
and ordernature in ('02','03')
and received_date > '01.01.2010'
and buyer_code = 'GAP'
group by order_number,received_date
order by received_date desc

When run in crystal reports does not pull the same data as sql server express. It is pulling only one record as opposed to 7. Anybody have some insight? Thank you in advance. (I have a suspicion it has something to do with the nested statement).

Answer 1

Usually I use SQL server profiler to check what SQL Statements Crystal is running behind the scenes. In your case is not possible because you are running SQL Server Express.

In Crystal you have the option of checking the actual SQL statement that Crystal is running by selecting "Database" and "Show SQL Statement" in the top menu.

Make sure that under Reports menu you are not putting any conditions in the Select Records.

Check that you are showing the fields in the detail and not in the header or footer. That is a common mistake.

Answer 2

Several possibilites come to mind.

First are you absolutely sure that Crystal Reports and the database you are querying are the same one. Amazing how often one is querying dev and another prod when you have these kinds of issues. Even if you don't believe this to be the case, check this anyway.

Permissions could be another reason for the discrepancy.

Finally confirm that the two queries are exactly the same. Use Profiler if possible to confirm this.