RE: Partial numeric field update in SQL

["Weatherly, Howard (Contractor) (DLIS)" <Howard.Weatherly@xxxxxxxxxxxx>]

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
I have not tried this, but it seem that if the column is numeric, the update
value should not be in ticks', note this is just a guess.

-----Original Message-----
From: Karl Keller [mailto:kkeller@girling.com]
Sent: Wednesday, December 11, 2002 12:20 PM
To: midrange-l@midrange.com
Subject: Partial numeric field update in SQL


This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Hi Group,

    I have a file with a 6 digit numeric field. In that field is a YYYYMM
date. I'm trying to update the year to the next year. 2002 to 2003.

    My statement is:

UPDATE LIBRARY.FILE
SET SUBSTR(DIGITS(FIELD),4,1) = '3'
WHERE SUBSTR(DIGITS(FIELD),4,1) = '2';

It's barfing on the ( between the first SUBSTR & DIGITS. The error I'm
getting is [SQL0104] Token ( was not valid.

However if I run the following Select statement:

Select * from LIBRARY.FILE Where (SUBSTR(DIGITS(FIELD),4,1)) = '2'

it executes successfully. I could have sworn that I have done updates like
this before! Can someone please explain to me why this doesn't work?

Thanks in advance.


Karl Keller
Information Systems
Girling Health Care, Inc.

Confidentiality Notice:  This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
and privileged information.  Any unauthorized review, use, disclosure or
distribution is prohibited.  If you are not the intended recipient, please
contact the sender by reply e-mail and destroy all copies of the original
message.
--

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.