RE: [SPAM 1]sql update question

Add a comma before QENVID (in the CREATE TABLE statement)... What's
happening now is that your construct (ADH_000001) is now named QENVID.
Is that what you wanted? If so, perhaps you should use AS to make it
clear that you are naming the field.

-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Tuesday, May 18, 2010 7:58 AM
To: Midrange Systems Technical Discussion
Subject: [SPAM 1]sql update question

Hi,

I'm sorry about posting this again, but something in my post is getting
it labelled as spam.


I've done my update like so :


/* Create temporary table with keyfields and derived field QENVID :

CREATE TABLE QTEMP/T AS (
SELECT 'ADH_' CONCAT SUBSTR ( DIGITS ( RANK() OVER ( ORDER BY QCLTDS,
QADRDS )), 5, 16) QENVID, QLOTCD, QCLTDS, QADRDS, QADHNO
FROM AVSENT ) WITH DATA


/* Update the main table with the derived value in the temporary table :

UPDATE AVSENT U
SET QENVID = (
SELECT T.QENVID
FROM T
WHERE
U.QLOTCD = T.QLOTCD AND
U.QCLTDS = T.QCLTDS AND
U.QADRDS = T.QADRDS AND
U.QADHNO = T.QADHNO
)


I am a little worried that there may be an error in my code. If anyone
could point it out or suggest an easier method I would be obliged.
Thanks.