RE: Join two files with different time format -- RPG400-L

Character is a very slow and lofty join process in SQL, especially in a conversion.

Let's be clear, first, about the expression in question. This is not a join expression, it is a criteria expression. Join clauses occur in the body of the FROM statement.

You already have the formula for creating your join in your SELECT statement: (YEAR(ALV.RSTDTE)* 10000) + (MONTH(ALV.RSTDTE)* 100) + (DAY(ALV.RSTDTE)). Use this to create the join between the files. In other words:

FROM STPALV ALV INNER JOIN ATS002P SCD ON ALV.EMPNUM = SCD.A2EMPNO AND
ALV.LOCN = SCD.A2LOCN AND (YEAR(ALV.RSTDTE) * 10000) + (MONTH(ALV.RSTDTE) * 100) + (DAY(ALV.RSTDTE)) = SCD.A2DATE

SQL doesn't care about the native data type in numeric comparisons. Converting the data to match type is an unnecessary use of IO and processor.

There is yet another "radical" approach to this problem. Convert the decimal date to a real date or add a field to the file in question that contains a real date version of the data with a trigger.

One thing I've learned over time is that numeric date fields provide NO support for date validation outside of the programming language that writes the record. If you attempt to write July 32nd, for example, to a numeric date field, the database doesn't care. It's just another number. A date field, however, will throw an exception. I say this with the greatest sympathy for programmers that have to deal with this anomaly. Our predecessors created 6 digit numeric fields for dates in mmddyy format. There is no greater undertaking in the world of RPG and/or SQL than to compare two drastically unlike data types. You may be able to reduce the overhead by the conversion methods suggested, but your best bet in the long run is to make the data types the same in the file.

Tom Armbruster

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Peter Dow (ML)
Sent: Wednesday, May 07, 2008 2:34 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Join two files with different time format

Hi Esben,

Try converting the date to decimal, or both the date and the decimal
date to character, e.g.

dec(char(ALV.RSTDTE,ISO),8,0) = ALV.A2DATE

or

char(ALV.RSTDTE,ISO) = digits(ALV.A2DATE)

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Esben Kiel Sørensen wrote:

Hi all,

I'm trying to join two files, but the problem is that the files don't have the same date format. One is an 8 digits decimal date, and the other is a "real" data field '2008-04-25'.. How can i join these?

I tried:

DECLARE A CURSOR FOR
SELECT 'D' , ALV . EMPNUM , ALV . LVCDE , ( YEAR ( ALV . RSTDTE ) * 10000 ) + ( MONTH ( ALV . RSTDTE ) * 100 ) + ( DAY ( ALV . RSTDTE ) ) , ALV . RSTTME , ALV . LVEDUR , SCD . A2SSHIFT , ALV . SDTALV + 19000000 , : H , : H , ' ' , : H , : H

FROM STPALV ALV , ATS002P SCD

WHERE ALV . EMPNUM = SCD . A2EMPNO AND
ALV . LOCN = SCD . A2LOCN
AND LVESTS = '5' AND
ALV . RSTDTE = DATE ( INSERT ( INSERT ( DIGITS ( A2DATE ) , 5 , 0 , '-' ) , 8 , 0 , '-' ) )
AND ALV . EMPNUM < 80000 AND NOT EXISTS ( SELECT * FROM ATS006P WHERE ALV . LVCDE = A6LVCDE )
AND ALV . LVCDE NOT IN ( 'UNXAG' , 'TUNAX' , 'TUNXA' ) FOR READ ONLY

RSTDTE is the field defined like 10 L and A2DATE is a 8.0 decimal.

This works, but is take like for ever to prepare this cursor... Any suggestions, on how to make this perform better?
Yours sincerely / Med venlig hilsen

Esben Kiel Sørensen
Software Developer
E-mail: eksor@xxxxxxxxxx