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 ]
Karl,

I set up a little test and it failed both ways for me also so here is what I
did

UPDATE hgr0093.date_master
SET testcol = testcol - 00010000
WHERE SUBSTR(DIGITS(testcol),4,1) = '4'

testcol before = 12345678 testcol after - 12335678

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


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

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