RE: Another SQL question

UPDATE FILEA a SET (a.Field1, a.Field2) = 
  (
    SELECT b.Field1,b.Field2 
    FROM FILEB b
    WHERE SUBSTR(DIGITS(a.RUNDAT)) = b.WKEDAT
  )
WHERE EXISTS 
  (
    SELECT 1
    FROM FILEB b
    WHERE SUBSTR(DIGITS(a.RUNDAT)) = b.WKEDAT
  )

For this kind of update, you need to have 1-to-1 match on dates.  If there
ever is 1-to-many, you'll need to further enhance your WHERE clause to
ensure your natural key guarantees uniqueness.

Also, are all values (century,year,month,day) guaranteed to have 2 digit
representations?  For example, is January represented as 01 or 1?
Above listed join assumes a 2 digit match.

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rowe, Sheri
Sent: Wednesday, November 29, 2006 1:56 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Another SQL question

Hi,

After I sent this out, my boss decided to change the file I am trying to
update from 8 A to 8 S, so now I just need to compare a 6S field to a 8
S field. (so I spend the remainder or yesterday and today converting
existing programs to fit this new format) I have VERY limited SQL
skills, so would appreciate some help with this.

FILEA  is a data file, Date field is now 8 S   CCYYMMDD format
FILEB  is a calendar file, Date field is 6 S   YYMMDD Format  (Contains
Week end dates)

I need to update fields in FILEA.Field1, FILEA.Field2 from FILEB.Field1,
FILEB.Fiel2 if the date in field FILEA.RUNDAT exists in FILEB.WKEDAT.

Thanks.

Sheri 


   
   3. RE: Another SQL question. (Rick.Chevalier@xxxxxxxxxxxxxxx)
   4. Re: Another SQL question. (Alfredo Delgado)
   5. Re: Another SQL question. (rob@xxxxxxxxx)
   7. Re: Another SQL question. (Alan Shore)
   8. RE: Another SQL question. (Elvis Budimlic)
   
----------------------------------------------------------------------



message: 3
date: Tue, 28 Nov 2006 14:14:42 -0600
from: <Rick.Chevalier@xxxxxxxxxxxxxxx>
subject: RE: Another SQL question.


Sheri,

It's possible to compare them using DIGITS to convert the numeric date
to a character format.  The dates must be in the same format though and
in YYMMDD format.  If they are in MMDDYY format or the 8 byte date has 4
digit years you can't compare them.  You can build a work date field
using concatenate but that can get kind of complex.  Check the archives
for this list and the RPG list.  Date comparison and conversion in SQL
comes up pretty frequently.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rowe, Sheri
Sent: Tuesday, November 28, 2006 1:57 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Another SQL question.

I need to update fields in FILEA based on a date comparison
with FILEB.
However, the date in FILEA is defined as 8A and the date in
FILEB is defined as 6S.  Is it possible to compare these
fields even though they are not the same format?

It's a one-time fix and I was hoping avoid writing a program.

Thanks.

Sheri Rowe
Timex Canada

------------------------------

message: 4
date: Tue, 28 Nov 2006 15:14:57 -0500
from: Alfredo Delgado <adelgado@xxxxxxxxxxxxxxxxx>
subject: Re: Another SQL question.

Sheri, you can manipulate the values to compare them. Do you have sample

data -- with datatypes -- you could share for further help?

Thanks,
Alfred

Rowe, Sheri wrote:

I need to update fields in FILEA based on a date comparison with FILEB.
However, the date in FILEA is defined as 8A and the date in FILEB is
defined as 6S.  Is it possible to compare these fields even though they
are not the same format?

It's a one-time fix and I was hoping avoid writing a program.

Thanks. 

Sheri Rowe
Timex Canada

 

------------------------------

message: 5
date: Tue, 28 Nov 2006 15:16:39 -0500
from: rob@xxxxxxxxx
subject: Re: Another SQL question.

Other than the dangers of assuming the century it can be done.  Make
sure 
you don't blank out values that are not in the other file.  That's why I

use the Inner Join.

CREATE TABLE ROB/TIMEXA 

(DATE8 CHAR (8 ) NOT NULL WITH DEFAULT,
  BIGSELLER CHAR (15 ) NOT NULL WITH DEFAULT) 
INSERT INTO ROB/TIMEXA VALUES('20061110', 'ITEMA')
INSERT INTO ROB/TIMEXA VALUES('20061111', 'ITEMB')
INSERT INTO ROB/TIMEXA VALUES('20061112', 'ITEMC')

CREATE TABLE ROB/TIMEXB 
(DATE6 CHAR (6 ) NOT NULL WITH DEFAULT,
BIGSELLER CHAR ( 15) NOT NULL WITH DEFAULT) 
INSERT INTO ROB/TIMEXB VALUES('061111', 'ITEMQ')

select * from timexA
....+....1....+....
DATE8     BIGSELLER
20061110  ITEMA 
20061111  ITEMB 
20061112  ITEMC 

select * from timexB
....+....1....+..
DATE6   BIGSELLER
061111  ITEMQ 

(Note:  Your date formats may differ.  Like, DATE6 may be MMDDYY and not

YYMMDD.)
UPDATE timexa a 
    SET bigseller = 
         (SELECT b.bigseller FROM timexB b 
         WHERE substr(a.date8,3,6) = b.date6 
           AND a.bigseller <> b.bigseller) 
    WHERE substr(a.date8,3,6) IN 
         (SELECT substr(a.date8,3,6) from timexa A
          INNER JOIN timexb b 
          ON substr(a.date8,3,6)=b.date6 
          AND a.bigseller <> b.bigseller) 
1 rows updated in TIMEXA in ROB.

select * from timexA
....+....1....+....
DATE8     BIGSELLER
20061110  ITEMA 
20061111  ITEMQ 
20061112  ITEMC 

Rob Berendt