RE: Join two files with different time format

[Esben Kiel Sørensen <eksor@xxxxxxxxxx>]

Hi,

Thanks for input! 

If I put the join in, then I won't have to have it in my SELECT part, do I?  

Select * 

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          

WHERE                                                                   
                             
       LVESTS = '5'             AND                                     
       ALV.EMPNUM < 80000       AND                                     
       NOT EXISTS (SELECT * from ats006p WHERE ALV.LVCDE = A6LVCDE)  and
       ALV.LVCDE  not in ('UNXAG', 'TUNAX', 'TUNXA')                    


Yours sincerely / Med venlig hilsen

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




-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Armbruster, Tom
Sent: 8. maj 2008 00:55
To: RPG programming on the AS400 / iSeries
Subject: RE: Join two files with different time format

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
>
>
>   
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.