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

 

[synapsevampire]

You last post of that thread asked if something was incorrect, as opposed to stating if you were still getting errors.

I had asked for the Oracle connectivity being used also. Check this using Database->Set Location, although you should know how you connected to the database, and please include the database version.

Try this in a SQL Expression:

to_date('12/1/2004','MM/DD/YYYY')

If it works, and your date field has the same format, it should be fine to replace it with your field in a QSL Expression.

I suggest using either the CR supplied ODBC or native connectivity, not the Oracle ODBC driver.

-k

 

[Turkbear]

What is the format in Oracle? If a true Date field then
Make your parameter a date type say {?OldestDate}
then your selection formula would be

{SHIPMENTS.SHIPDATE}> {?OldestDate}

Or to specify it literally ( no parameter):
{SHIPMENTS.SHIPDATE}> to_date('01012004','MMDDYYYY')

Assumes {SHIPMENTS.SHIPDATE} is an Oracle DATE type field in your database, and that Time is not important.( If you create an index on the shipdate field it can speed it up a lot - depending on what proportion of the records are > the supplied date).


Also, be sure to use either CR's Oracle ODBC or ( better , in my opinion) Oracle Native connection
If I'm missing something let me know..This should not be difficult to do, we do it all the time.

 

[Monkey820]

The driver I am connecting with is CROR815.DLL version 03.60.0016

I am sorry, but I do not understand how to do this. That is why I am asking for help.

"Try this in a SQL Expression:

to_date('12/1/2004','MM/DD/YYYY')

If it works, and your date field has the same format, it should be fine to replace it with your field in a QSL Expression."

I do not know what you mean "it should be fine to replace it with your field in a sql expression"

 

[Monkey820]

Turkbear - How do I look for the format? Do you mean...'MM/DD/YYYY' ?
Bcz the field does have a date-time format.
'DD/MM/YYYY HH24:MI:SS'

 

[Monkey820]

sorry, it is 'YYYY/MM/DD HH24:MI:SS'

 

[synapsevampire]

You don't look for formats of a Datetime type, Oracle decides how to store it (or your dba may alter this).

the important thing might be to react to suggestions, such as the hardcoded test I gave you.

But you didn't. so I can't help.

Paste it in a SQL Expression, and post the results.

-k

 

[Monkey820]

Ok- now I have the report doing the query without errors but, it will not bring data in from any date or date range previous to today.

Here is the SQL statement:
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS')

Here is my record selection:
{%ShipDate} = {?Date}

Here is the SQL Query:
SELECT
"SHIPMENTS"."PACKSLIPNO", "SHIPMENTS"."SHIPDATE", "SHIPMENTS"."SHIP_TO_ATTN", "SHIPMENTS"."SHIP_TO_ADDR1", "SHIPMENTS"."SHIP_TO_ADDR2", "SHIPMENTS"."SHIP_TO_CITY", "SHIPMENTS"."SHIP_TO_STATE", "SHIPMENTS"."SHIP_TO_ZIP", "SHIPMENTS"."FREIGHT_DESCRIP", "SHIPMENT_DTL"."RELEASES_ID", "V_SHIP_HIST"."MISC_ITEM", "V_SHIP_HIST"."ITEMNO", "V_SHIP_HIST"."DESCRIP", "V_SHIP_HIST"."REV", "SHIPMENT_BOL"."TOTAL_BOXES", "SHIPMENT_BOL"."WEIGHT", "SHIPMENT_RELEASES"."LOTNO", "SHIPMENT_RELEASES"."QTYSHIPPED", "AKA"."CUST_ITEMNO", "AKA"."CUST_DESCRIP",
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS')
FROM
"IQMS"."SHIPMENTS" "SHIPMENTS",
"IQMS"."SHIPMENT_DTL" "SHIPMENT_DTL",
"IQMS"."V_SHIP_HIST" "V_SHIP_HIST",
"IQMS"."SHIPMENT_BOL" "SHIPMENT_BOL",
"IQMS"."SHIPMENT_RELEASES" "SHIPMENT_RELEASES",
"IQMS"."AKA" "AKA"
WHERE
"SHIPMENTS"."ID" = "SHIPMENT_DTL"."SHIPMENTS_ID"(+) AND
"SHIPMENT_DTL"."ID" = "V_SHIP_HIST"."SHIPMENT_DTL_ID"(+) AND
"SHIPMENT_DTL"."ID" = "SHIPMENT_BOL"."SHIPMENT_DTL_ID"(+) AND
"SHIPMENT_DTL"."ID" = "SHIPMENT_RELEASES"."SHIPMENT_DTL_ID"(+) AND
"V_SHIP_HIST"."ARINVT_ID" = "AKA"."ARINVT_ID"(+) AND
"V_SHIP_HIST"."ARCUSTO_ID" = "AKA"."ARCUSTO_ID"(+) AND
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS') >= TO_DATE ('12-04-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS') <= TO_DATE ('12-04-2004 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
ORDER BY
"SHIPMENTS"."SHIP_TO_ATTN" ASC,
"SHIPMENTS"."SHIPDATE" ASC


Please let me know what I am doing incorrectly. I appreciate your help very much!

 

[synapsevampire]

You didn't paste what's in your record selection formula, so it makes it hard to know how this SQL was developed.

It should have something like:

{%My SQL Expression} in {?My Date Range Parameter}

The result in the Database->Show SQL Query should reflect the dates in the parameter.

-k

 

[Monkey820]

I have
{%ShipDate} = {?Date}

in my record selection.

 

[Monkey820]

I am still only able to retrieve data for the current date.
Any direction is appreciated.

Thanks,
Gertie

 

[Turkbear]

Hi,
If SHIPDATE is a DATE ( or TIMESTAMP) TYPE, do NOT use the To_Date function on that field - It is already a date.

Use ( for the Date part of the Sql):

{"SHIPMENTS"."SHIPDATE"} >= TO_DATE ('12-04-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
    {"SHIPMENTS"."SHIPDATE" <= TO_DATE ('12-04-2004 23:59:59', 'DD-MM-YYYY HH24:MI:SS')

Can you, from SqlPlus, do a DESC SHIPMENTS and post the output?

 

[Monkey820]

Thank you Turkbear,

When I enter the code in that you gave me, I get the error "ORA-00923-FROM keyword not found where expected"
and it places the cursor before the first "to_date".

I can connect with SQL*Plus but I do not know how to get what you are looking for.
I keep getting "connection description for remote database not found"

(I'm sorry I simply do not have this knowledge, I very much appreciate your help)

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Apr 20 13:10:10 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

 

[Turkbear]

Hi,
I was not as clear as I should have been..The code I posted was for the WHERE clause..

In the SELECT statement, remove the
TO_DATE("SHIPMENTS"."SHIPDATE", 'YYYY/MM/DD HH24:MI:SS')
and just use
"SHIPMENTS"."SHIPDATE"

 

[Monkey820]

ok, now my parameter is not working bcz it is looking for a string.

When I put my cursor over the ship date it shows
SHIPMENTS.SHIPDATE(string)

the way is was set up orginally (the report that takes 4 -6 minutes to load) was like this:

parameter:
{@shipdate}= {?date}

@shipdate:
DTSToDate ({SHIPMENTS.SHIPDATE})

and
?date:
date range

Thank you for your continued help
Gertie

 

[synapsevampire]

Turk: your advice, though well founded, isn't always true.

A datetime in oracle and a date only parameter in Crystal MAY not result in the sql getting passed, it depends upon many factors.

But if you change the format of the date, you'll find that CR will generally pass it...

I bailed out of this thread because of all of the cooks (per usual).

-k

 

[Turkbear]

Hi,
Thanks, -k..I agree about the cooks ( and the Date stuff.There is really no substitute for using 'Show Sql' before running a complex report).

Gertie,
That's why I qualified my response with 'if it is a DATE type '

To check a string field against a Date type parameter the database field would need converting before the test so
create a SqlExpression and use that in the where clause:

{@strtodate}
to_date({SHIPMENTS.SHIPDATE},'DD-MM-YYYY HH24:MI:SS')

Then use
@strtodate = {?date}
Be sure your ?date parameter is a DateTime Range.

Check with Show Sql to see what is actually sent to Oracle and,assuming a pass-through, you could add a function-based index to the SHIPMENTS.SHIPDATE field in the Oracle table to speed things up.

 

[Monkey820]

Thank you Turkbear.

The "to_date" is not recognized in the crystal syntax.

However, I think that this is how is was orginally set up.

@shipdate:
DTSToDate ({SHIPMENTS.SHIPDATE})

then record selection:
{@shipdate} = {?Date}

Here is the SELECT statement
SELECT
"SHIPMENTS"."PACKSLIPNO", "SHIPMENTS"."SHIPDATE", "SHIPMENTS"."SHIP_TO_ATTN", "SHIPMENTS"."SHIP_TO_ADDR1", "SHIPMENTS"."SHIP_TO_ADDR2", "SHIPMENTS"."SHIP_TO_CITY", "SHIPMENTS"."SHIP_TO_STATE", "SHIPMENTS"."SHIP_TO_ZIP", "SHIPMENTS"."FREIGHT_DESCRIP", "SHIPMENT_DTL"."RELEASES_ID", "V_SHIP_HIST"."MISC_ITEM", "V_SHIP_HIST"."ITEMNO", "V_SHIP_HIST"."DESCRIP", "V_SHIP_HIST"."REV", "SHIPMENT_BOL"."TOTAL_BOXES", "SHIPMENT_BOL"."WEIGHT", "SHIPMENT_RELEASES"."LOTNO", "SHIPMENT_RELEASES"."QTYSHIPPED", "AKA"."CUST_ITEMNO", "AKA"."CUST_DESCRIP"


Plus this is going thru years of data.
I was hoping to somehow on the server ask it to look at only this year before bringing it to the workstation to select the date range.

Gertie

 

[Turkbear]

Hi,
to quote one of my favorite movies:
"What we have here is a failure to communicate"
I indicated that you could create a SqlExpression and use that in the where clause:
This means using Sql syntax functions like Oracle's to_date(), which are NOT Crystal syntax.
By using the Sql expression in the where clause, it should restrict the data to the range specified before returning any data to Crystal....But, to be sure, look at the Sql generated after using it.

 

[Monkey820]

I think the failure to communicate is due to the lack of my knowledge. I am sorry for that.
To create an SQL Expression, I only know how to do thru Crystal Reports. I don't know how to create it otherwise. I don't know how to use it in the where clause.
Again, I am sorry.

Gertie