RE: Partial numeric field update in SQL

["Karl Keller" <kkeller@xxxxxxxxxxx>]

This is a multi-part message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Howard Weatherly wrote:

<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.>

Howard, I have tried both ways with the same result.

Thanks for your response!

Karl Keller



-----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.
(512) 458-3090 ext. 5040

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.
--