dissimalar dates between DBs

[kieso]

I have an HS400 table and a Oracle table

Crystal can see them both, but the dates are quite different

The Oracle database shows the date that the product was delivered, and always assumes midnight in it's date/time field

9/24/2002 12:00:00AM

The same delivery on the HS400 shows the date that the order was selected (always the prior calander day), as a number field

20020923

Will there be anyway of linking these two fields in Crystal, or do I have to build an automatic IBM query to build me a new report?

Thanks in advance

Rodger

 

[lyanch]

Do you really want to link on the field? Or use it as a parameter (ie date = yesterday)? Linking on it looks like a many to many... which can get very ugly results very quickly.

lisa

 

[synapsevampire]

You can create a SQL Expression in Crystal, or a View in Oracle and cast the Oracle date to match the HS400 date format.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kieso]

Okay!

I'm getting closer, I'm sure of it!

I've skimmed the AS400 of just the prior days orders, and I'm going to have our dispatcher only run this report for the current day. It's a bit of cheating, I know, but at least it gets some results.

Now I have a new problem - Crystal won't link the AS400 and Oracle by the customer number. When I browse each of the two fields, I get the following
Oracle database LOCATIONID string 15 characters
"001085 "
AS400 database P5CUSN string 10 characters
" 001085"

I am planning on redoing the AS400 query to give me a 15 character string with the spaces on the end, unless someone has a Crystal solution

Rodger

 

[synapsevampire]

You might create a SQL Expression to trim it, and link on that.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kieso]

Hey, synapsevampire

Thanks for the suggestion, but how do I do that? Do you mean to say that I should build this SQL trim expression right into the SQL code of my crystal report?

 

[synapsevampire]

If you have version 9, you can jsut create the SQL straight away and use it.

If you have CR 8-8.5, select Insert->Field Object->Right Click SQL Expression Fields->New and enter the sql as AS400 would expect it to create the trimmed field. This will allow you to do comparisons and display in the same format.

You can edit the FROM part of the SQL directly in CR (Database to use a trimmed filed as part of the join.

For instance in SQL Server it would be ltrim(table.field)

An example might be something like:

SELECT
AtsAddrs."guiUniqueID",
AtsAddrs."guiEntityID",
AtsAddrs."chvEntityCode",
AtsAddrs."dtmEffDate",
B."dtmEffDate"
FROM
YRS.dbo.AtsAddrs AtsAddrs INNER JOIN YRS.dbo.AtsAddrs B ON ltrim(AtsAddrs."chvAddr1&quot = ltrim(B."chvAddr1&quot

You'd have your trim syntax on just one side though.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kieso]

Well, I must concede to being rather dim when it comes to SQL, but I can't figure this stuff out at all.

I went into Crystal and added RSSTOPBUN1 and linked RSSTOPBUN1.LOCATIONID to STOP.LOCATIONID, and brought RSSTOPBUN1.P5TCON01 into my report.

For some reason, none of that stuff appears in the SQL query that I've included below.


SELECT
TRIP."TRIPDATE", TRIP."TRIPID", TRIP."DRIVER1NAME", TRIP."PLANNEDDEPARTURE", TRIP."ACTUALDEPARTURE", TRIP."PLANNEDARRIVAL", TRIP."ACTUALARRIVAL",
STOP."TRIPDATE", STOP."INTERNALSTOPID", STOP."LOCATIONID", STOP."PLANNEDARRIVAL", STOP."ACTUALARRIVAL", STOP."PLANNEDDEPARTURE", STOP."ACTUALDEPARTURE",
LOCATION."DESCRIPTION",
PRODUCTORDER."PLANNEDSIZE1"
FROM
"DMSDBA"."TRIP" TRIP,
"DMSDBA"."STOP" STOP,
"DMSDBA"."LOCATION" LOCATION,
"DMSDBA"."PRODUCTORDER" PRODUCTORDER
WHERE
TRIP."TRIPDATE" = STOP."TRIPDATE" AND
TRIP."TRIPID" = STOP."TRIPID" AND
STOP."LOCATIONID" = LOCATION."ID" AND
STOP."CUSTOMERID" = PRODUCTORDER."CUSTOMERID" AND
STOP."TRIPDATE" = PRODUCTORDER."TRIPDATE" AND
STOP."TRIPID" = PRODUCTORDER."TRIPID" AND
STOP."INTERNALSTOPID" = PRODUCTORDER."INTERNALSTOPID" AND
STOP."TRIPDATE" >= ? AND
STOP.&quot;TRIPDATE&quot; < ?


How do I get RSSTOPBUN1 into my report.

Thanks

Rodger

 

[kieso]

Sorry. Neglected to add that I'm using Crystal 7.0 since the reports are being used by a rather antiquated Crystal viewer called UPS logistics. That viewer isn't the problem though, since these issues occur before it's ever uploaded there

Thanks again

Rodger

 

[kieso]

Credit where it's due, right...

I knocked around the Tek-Tips sight and found a forum on ANSI SQL where I found a really awesome Fax faq220-822

It points to a couple of SQL tutorials. I've been playing with the first turorial for 20 minutes, and I think I already have the answer to my very basic question

Kudo's to Simanek's really great FAQ's post

)

Rodger