Re: Partial numeric field update in SQL

 Would it be easier just to add 100 to the already numeric field?  And a
whole lot faster?



---------------------------------------------------------
Booth Martin   http://www.MartinVT.com
Booth@MartinVT.com
---------------------------------------------------------

-------Original Message-------

From: midrange-l@midrange.com
Date: Wednesday, December 11, 2002 12:20:00 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.

.