how to compare a field in two tables in crystal reports?
I've two tables in SQL server 05: Account n Vehicle and both tables have a common field account key. Now i need to create a report in which i need to: Count the number of Account keys on the Account n Vehicle tables. And, if they don't match i need to display the non-matching Account Key and following information in crystal reports. Account.Account_key Account.Account_No Vehicle.Vehicle_key 100 A1001 X123 How to i achieve this in Crystal reports? Help!
Solution: how to compare a field in two tables in crystal reports?
mlmcc,
COALESCE returns the first argument that's not null. In this case, it's like if IsNull (v.account_key) then 0 else v.account_key
baralp,
I don't know if this will affect the performance, but assuming that account_key will never be null or 0 in either table, I'd use this for the WHERE:
where a.account_key is NULL
Looking at both of your posts, you seem to be looking for account_key values that are in one table, but not the other, but in your first post you seem to be looking for fields from both tables (Account.Account_key, Account.Account_No and Vehicle.Vehicle_key). By definition, if you're looking at the account_key values that are in one table and not the other, you will only have data from one table, not both.
So, I'm not sure exactly what you're trying to do.