SQL expression in report

[apples]

Hi, I hope you can give me a quick solution. I am using crystal 9. I am still learning crystal, so please excuse if my wording is not too clear.
I am using the select expert to filter out some data but in addition I have to use a SQL expression as well. I created a formula in the SQL expression editor as follows but its giving me an error it does not like 'select'. So I have to fix this problem and know how to incorporate it into my select expert formula.
Please help. thanks
SELECT * from tblPolicyDetails A
WHERE EffectiveDate in(SELECT MAX(EffectiveDate FROM tblPolicyDetails B
WHERE A.RiskID = B.RiskID)

 

[rhinok]

A SQL Expression is similar to a Crystal Formula. Check out the following FAQ: faq767-1377. Its old, but hopefully still informative.

Since you're using CR9, write a SQL Command instead of a SQL Expression. SQL Commands can be used as tables.

 

[trezlub]

The SQL error is occuring because your syntax is wrong. Try this instead:

SELECT * from tblPolicyDetails A
WHERE EffectiveDate in(SELECT MAX(EffectiveDate) FROM tblPolicyDetails B
WHERE A.RiskID = B.RiskID)