How to Query on Server

[Monkey820]

Hi There!
I have posted before but am still having the same problem of speeding up a report.
I need the report to select the data on the server. I do not know how to do this.
Any help will be greatly appreciated.
the previous post is at

http://www.tek-tips.com/viewthread.cfm?SQID=822014&SPID=766&page=2

I need to select records no earlier than 1/1/2004.
In record selection I have:

{@ShipDate} = {?Date}

Crystal Reports 8.5
Oracle Server

@shipdate: DTSToDate ({SHIPMENTS.SHIPDATE})
?Date: Date Range Value

I have tried:
TO_DATE(SHIPMENTS.SHIPDATE,'MM/DD/YYYY')
but keep getting format errors

Any help is appreciated

Thanks,
Gertie

 

[Turkbear]

Hi,
That is Ok, learning is a process and is never over:

In the field explorer ( where you create parameters, formulas, etc) there is an option to create a new Sql Expression - name it something meaningful ( like OraDate) and use the TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS') as the expression.

In your selection formula
use
@OraDate = {?Date}

 

[Monkey820]

OK - That got me past recieving error msgs. No more error msgs. But, I am not receiving data back. Blank Form.
It brings the time to load from 3 1/2 minutes to less than 30 seconds, but no data.

Gertie

 

[dtempleton]

Hi Gertie,

It sounds like you're getting closer!

What datetime range are you using for {?Date} ?
And can you show us an example of a datetime value from "SHIPMENTS"."SHIPDATE" ?

Thanks,
Dave

 

[Monkey820]

Here is the sql query for the range:
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS')

But for the date parameter, they enter a date range for example of 4/1/04 to 4/30/04.

Is this what you are asking for?

 

[tektipdjango]

assuming as you said the format of the date like
'YYYY/MM/DD HH24:MI:SS'
your query must be something like this if you want the records not earlier than 1/1/2004 :

SELECT

blah blah

FROM

blah blah

WHERE
.... and
("SHIPMENTS"."SHIPDATE" >= '2004/01/01')

ORDER BY

blah blah


If you want those on april 12th change where clause to

("SHIPMENTS"."SHIPDATE" >= '2004/04/12') and ("SHIPMENTS"."SHIPDATE" < '2004/04/13')

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​