dbTalk Databases Forums  

INCLUDE column in an UPDATE of a fullselect

Go Back dbTalk Databases Forums Databases comp.databases.ibm-db2 INCLUDE column in an UPDATE of a fullselect

comp.databases.ibm-db2 comp.databases.ibm-db2


Discuss INCLUDE column in an UPDATE of a fullselect in the comp.databases.ibm-db2 forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
jefftyzzer
 
Posts: n/a

Default INCLUDE column in an UPDATE of a fullselect - 10-10-2007 , 04:24 PM





Colleagues:

Is possible to have an INCLUDE column in an UPDATE of a fullselect,
something like:

<SNIP>
FROM
FINAL TABLE
(
UPDATE
(
SELECT
GRP_NUM,
MIN(GRP_NUM) OVER (PARTITION BY EMPR_ID)
FROM
SESSION.NM_BAG
) AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = NMB1.GRP_NUM,
NMB1.GRP_NUM = MIN_GRP_NUM_FOR_EMPR
<SNIP>

--Jeff


Reply With Quote
  #2  
Old   
Serge Rielau
 
Posts: n/a

Default Re: INCLUDE column in an UPDATE of a fullselect - 10-10-2007 , 04:49 PM





jefftyzzer wrote:
Quote:
Colleagues:

Is possible to have an INCLUDE column in an UPDATE of a fullselect,
something like:

SNIP
FROM
FINAL TABLE
(
UPDATE
(
SELECT
GRP_NUM,
MIN(GRP_NUM) OVER (PARTITION BY EMPR_ID)
FROM
SESSION.NM_BAG
) AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = NMB1.GRP_NUM,
NMB1.GRP_NUM = MIN_GRP_NUM_FOR_EMPR
SNIP

--Jeff

Yes. UPDATE is the main reason for this feature, so you can pass out the
OLD value using NEW/FINAL TABLE.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab


Reply With Quote
  #3  
Old   
jefftyzzer
 
Posts: n/a

Default Re: INCLUDE column in an UPDATE of a fullselect - 10-10-2007 , 06:15 PM


On Oct 10, 2:49 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Quote:
jefftyzzer wrote:
Colleagues:

Is possible to have an INCLUDE column in an UPDATE of a fullselect,
something like:

SNIP
FROM
FINAL TABLE
(
UPDATE
(
SELECT
GRP_NUM,
MIN(GRP_NUM) OVER (PARTITION BY EMPR_ID)
FROM
SESSION.NM_BAG
) AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = NMB1.GRP_NUM,
NMB1.GRP_NUM = MIN_GRP_NUM_FOR_EMPR
SNIP

--Jeff

Yes. UPDATE is the main reason for this feature, so you can pass out the
OLD value using NEW/FINAL TABLE.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -
OK--thank you. So, in the example from the SQL Ref V2, below, where
would the INCLUDE go?

UPDATE (SELECT SALARY,
COMM,
AVG(SALARY) OVER (PARTITION BY WORKDEPT),
AVG(COMM) OVER (PARTITION BY WORKDEPT)
FROM EMPLOYEE)
AS E(SALARY, COMM, AVGSAL, AVGCOMM)
SET (SALARY, COMM)
= (AVGSAL, AVGCOMM)
WHERE EU.EMPNO = '000120'

I ask because, using the code I asked about in my first post, I'm
getting

SQL0206N "OLD_GRP_NUM" is not valid in the context where it is used.
LINE
NUMBER=210. SQLSTATE=42703

when I try to create the SP. If you're saying it's OK to have an
INCLUDE along with the UPDATE of the *fullselect*, then at least that
rules that out as an issue; perhaps I'm just putting the INCLUDE in
the wrong place.

Regards,

--Jeff



Reply With Quote
  #4  
Old   
Serge Rielau
 
Posts: n/a

Default Re: INCLUDE column in an UPDATE of a fullselect - 10-10-2007 , 06:29 PM


jefftyzzer wrote:
Quote:
On Oct 10, 2:49 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
jefftyzzer wrote:
Colleagues:
Is possible to have an INCLUDE column in an UPDATE of a fullselect,
something like:
SNIP
FROM
FINAL TABLE
(
UPDATE
(
SELECT
GRP_NUM,
MIN(GRP_NUM) OVER (PARTITION BY EMPR_ID)
FROM
SESSION.NM_BAG
) AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = NMB1.GRP_NUM,
NMB1.GRP_NUM = MIN_GRP_NUM_FOR_EMPR
SNIP
--Jeff
Yes. UPDATE is the main reason for this feature, so you can pass out the
OLD value using NEW/FINAL TABLE.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

OK--thank you. So, in the example from the SQL Ref V2, below, where
would the INCLUDE go?

UPDATE (SELECT SALARY,
COMM,
AVG(SALARY) OVER (PARTITION BY WORKDEPT),
AVG(COMM) OVER (PARTITION BY WORKDEPT)
FROM EMPLOYEE)
AS E(SALARY, COMM, AVGSAL, AVGCOMM)
SET (SALARY, COMM)
= (AVGSAL, AVGCOMM)
WHERE EU.EMPNO = '000120'

I ask because, using the code I asked about in my first post, I'm
getting

SQL0206N "OLD_GRP_NUM" is not valid in the context where it is used.
LINE
NUMBER=210. SQLSTATE=42703

when I try to create the SP. If you're saying it's OK to have an
INCLUDE along with the UPDATE of the *fullselect*, then at least that
rules that out as an issue; perhaps I'm just putting the INCLUDE in
the wrong place.
That would be a -104 (syntax error).
The statement looks sane to me.
Could it be you have a local variable or parameter of the same name?
Perhaps make sure that the names are unique.
If that doesn't work I'd like to get a repro from you that I can play with.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab


Reply With Quote
  #5  
Old   
jefftyzzer
 
Posts: n/a

Default Re: INCLUDE column in an UPDATE of a fullselect - 10-10-2007 , 08:29 PM


On Oct 10, 4:29 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
Quote:
jefftyzzer wrote:
On Oct 10, 2:49 pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
jefftyzzer wrote:
Colleagues:
Is possible to have an INCLUDE column in an UPDATE of a fullselect,
something like:
SNIP
FROM
FINAL TABLE
(
UPDATE
(
SELECT
GRP_NUM,
MIN(GRP_NUM) OVER (PARTITION BY EMPR_ID)
FROM
SESSION.NM_BAG
) AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)
INCLUDE
(OLD_GRP_NUM BIGINT)
SET
OLD_GRP_NUM = NMB1.GRP_NUM,
NMB1.GRP_NUM = MIN_GRP_NUM_FOR_EMPR
SNIP
--Jeff
Yes. UPDATE is the main reason for this feature, so you can pass out the
OLD value using NEW/FINAL TABLE.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

OK--thank you. So, in the example from the SQL Ref V2, below, where
would the INCLUDE go?

UPDATE (SELECT SALARY,
COMM,
AVG(SALARY) OVER (PARTITION BY WORKDEPT),
AVG(COMM) OVER (PARTITION BY WORKDEPT)
FROM EMPLOYEE)
AS E(SALARY, COMM, AVGSAL, AVGCOMM)
SET (SALARY, COMM)
= (AVGSAL, AVGCOMM)
WHERE EU.EMPNO = '000120'

I ask because, using the code I asked about in my first post, I'm
getting

SQL0206N "OLD_GRP_NUM" is not valid in the context where it is used.
LINE
NUMBER=210. SQLSTATE=42703

when I try to create the SP. If you're saying it's OK to have an
INCLUDE along with the UPDATE of the *fullselect*, then at least that
rules that out as an issue; perhaps I'm just putting the INCLUDE in
the wrong place.

That would be a -104 (syntax error).
The statement looks sane to me.
Could it be you have a local variable or parameter of the same name?
Perhaps make sure that the names are unique.
If that doesn't work I'd like to get a repro from you that I can play with.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -
Changing the line "AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)" to "AS
NMB1"--that is, getting rid of the column names in the correlation
clause--did the trick.

--Jeff



Reply With Quote
  #6  
Old   
Serge Rielau
 
Posts: n/a

Default Re: INCLUDE column in an UPDATE of a fullselect - 10-11-2007 , 05:50 AM


jefftyzzer wrote:
Quote:
Changing the line "AS NMB1 (GRP_NUM, MIN_GRP_NUM_FOR_EMPR)" to "AS
NMB1"--that is, getting rid of the column names in the correlation
clause--did the trick.
Glad you got it to work. There is still something funny though and I'll
try to get to the bottom of it offline.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top