Re: Another SQL question

Hi Sheri
try the following

UPDATE FILEA A SET F1 = 'Whatever value'
WHERE EXISTS (
  SELECT *
  FROM FILEB B
  WHERE SUBSTR(DIGITS(A.DATE1),3,6) = DIGITS(B.DATE2))

or something like that



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx



midrange-l-bounces@xxxxxxxxxxxx wrote on 11/29/2006 02:56:05 PM:

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
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





--

message: 7
date: Tue, 28 Nov 2006 15:17:23 -0500
from: Alan Shore <AlanShore@xxxxxxxx>
subject: Re: Another SQL question.

Hi Sheri
the date comparisons can be done with a few assumptions
DATEA (from FileA) is in the format MMDDCCYY
DATEB (from FileB) is in the format MMDDYY

then
digits(substr(DATEA, 1, 4) || substr(DATEA, 7, 2)) = DATEB

or something like that



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx

midrange-l-bounces@xxxxxxxxxxxx wrote on 11/28/2006 02:56:43 PM:

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: 8
date: Tue, 28 Nov 2006 14:17:54 -0600
from: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
subject: RE: Another SQL question.

Yes.

Assumptions:
- FILEA.DATE1 is in format MMDDYYYY
- FILEB.DATE2 is in format MMDDYY

NOTE: you WILL need to massage the UPDATE statement to match exactly
what
you need.

Easiest may be to compare them as numeric values, rather than dates:

UPDATE FILEA a SET F1 = 'New value'
WHERE EXISTS (
  SELECT 1
  FROM FILEB b
  WHERE CAST(SUBSTR(a.DATE1,1,4) || SUBSTR(a.DATE1,7) AS NUMERIC(6))
        = b.DATE2
)

However, if you really want to transform both fields to DATE types, you
could do something like this:

UPDATE FILEA a SET F1 = 'New value'
WHERE EXISTS (
  SELECT 1
  FROM FILEB b
  WHERE DATE(SUBSTR(a.DATE1,1,2) || '/' ||
             SUBSTR(a.DATE1,3,2) || '/' ||
             SUBSTR(a.DATE1,5)) =
        DATE(SUBSTR(DIGITS(b.DATE2),1,2) || '/' ||
             SUBSTR(DIGITS(b.DATE2),3,2) || '/' ||
             SUBSTR(DIGITS(b.DATE2),5))
)

Hope that gives you an idea of how to approach it.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.