Having problem passing DateTime parameters from Crystal report to DB

[Plymouth425]

Hi,
My apology if this is a repeat.
I could not find a solution for this.
I am new to crystal and Oracle both.
We are using Crystal 9 and Oracle 9
I am having problem in passing DateTime type parameter in SQL expression.
Here are the details:
I have 2 parameter fields in my report.
1. StartDateTime
2. EndDateTime
Both are of type DateTime.
I have a SQL expression field. the code is here:
(
SELECT Count(*)
FROM
(
SELECT DISTINCT(ARDI.DILPN_ID)
FROM TS_AR_PROJDILPN ARDI, TS_AR_TRAORDER ARORDER
WHERE ARDI.STATUS = 'Not-Active' AND
ARDI.DILPN_ID = ARORDER.UNIT_ID AND
ARDI.CREATE_DATE >= {?StartDateTime} AND
ARDI.CREATE_DATE <= {?StartDateTime} AND
UPPER(SUBSTR(ARORDER.LOC_ID_PS,13,2)) = '1B' AND
UPPER(SUBSTR(ARORDER.LOC_ID_PS,7,1)) = 'B'
)DI
)
The problem is in passing the dates parameters.
If I remove those its fine.

The error thrown back reads as:

Query Engine Error: 'HY000:[Oracle][ODBC][Ora]ORA-24374: define not done before fetch or execute and fetch'.

Any help would be highly appreciated.
Thx

 

[vidru]

Crystal won't allow you to use parameter fields in SQL Expressions.

Since you're using CR9, why not base the report off a SQL Command, and run your subquery as part of the Command?

-dave

 

[synapsevampire]

to elaborate on Dave's suggestion, since you already have the SQL created, consider creating a View from the SQL.

Also your SQL could be

SELECT COUNT(DSITINCT(field)) from...

I would suggest creating a View along these lines:

SELECT ARDI.DILPN_ID, ARDI.CREATE_DATE
FROM TS_AR_PROJDILPN ARDI, TS_AR_TRAORDER ARORDER
WHERE ARDI.STATUS = 'Not-Active' AND
ARDI.DILPN_ID = ARORDER.UNIT_ID AND
UPPER(SUBSTR(ARORDER.LOC_ID_PS,13,2)) = '1B' AND
UPPER(SUBSTR(ARORDER.LOC_ID_PS,7,1)) = 'B'

Then you can pass the date criteria in SQL from CR and get good performance, plus have a reusable object. Add in any other fields that might prove useful to other reports/processes as they won't be returned unless you explicitly use them in your report.

-k

 

[Plymouth425]

Thanks for your suggestions.
I have made the view and sending start and end date from report.
My next hurdle is that I need to do some calculations based on how many records I get.
Any idea how can I get the number of records in a formula?