Help with Query

[ecugrad]

I have built the below sql statement in Oracle (Runs Fine) and need it to run in Crstal Reports CR 8.0. Not working for me, any help in changing what I need to changed will be appreciated

SELECT *
FROM BKRPTPROCESSLOG P, BKRPTWOBLOG W, FILENETUSERS F
WHERE W.WOBID = P.WOBID AND W.CREATEDDATE > TO_DATE('01/01/2006','MM/DD/YYYY')
AND P.DISPOSITION = 'S'
AND P.ENDTIME = (SELECT MAX(B.ENDTIME) FROM BKRPTPROCESSLOG B, BKRPTWOBLOG C WHERE B.WOBID = C.WOBID AND W.BANKRUPTCYNO = C.BANKRUPTCYNO)
AND P.USERNAME = F.USERNAME

Thanks,

Mike

 

[synapsevampire]

You can't paste this SQL into CR 8, btw, CR 8 is over 5 years old, consider an upgrade, later versions allow for pasting in SQL statements to a Command Object.

Well there is a way to paste SQL in older versions, you select ADO to do so, but then you can't use a record selection intelligently against it.

I suggets building a View on Oracle.

Or you can add the tables in to Crystal, and make the appropriate joins.

Then create a SQL Expression of:

{%MySQLExpression}
(SELECT MAX(B.ENDTIME) FROM BKRPTPROCESSLOG)

and then in the Report->Selection Formula->Record place:

{W.CREATEDDATE} > cdate(2006,1,1)
and
{P.DISPOSITION} = 'S'
and
P.ENDTIME = {%MySQLExpression}

The SQL Expression is the only tricky part.

-k