|
View previous topic :: View next topic
|
| Author |
Message |
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
I did see a couple of threads out there but did not get anywhere using the suggestions.. we are using db2 v9.1. I tried using the merge statement first using the VALUES and that worked fine.
The second format of the statement keeps giving me an error. I do not understand why
My statement was
MERGE INTO DB1.TABLE1 TAB1
USING
(
SELECT COL1, COL2
FROM DB1.TABLE2
WHERE COL1 = 3
) AS TAB2 (COL1, COL2)
ON TAB1.COL1 = TAB2.COL1 // Note that COL1 in both the tables are the primary keys and have the same values i.e. they will always match
WHEN MATCHED THEN
UPDATE SET TAB2.COL2 = TAB1.COL2
WHEN NOT MATCHED THEN
INSERT (COL1, COL2 )
VALUES (TAB2.COL1,TAB2.COL2)
I get the error
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD SELECT. TOKEN VALUES
WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 224 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'000000E0' X'000001FA' SQL DIAGNOSTIC INFORMATION
Appreciate help in advance |
|
| Back to top |
|
 |
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| I should have asked: Is this format of merge only valid in db2-udb and not on the z/os |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
MERGE is valid in DB2 Z/os.
This may help - the MERGE info from the db2 v 9 manual:
Nowhere that i have a logon has v9 yet. . .
I don't know if MERGE will work if your system is still in compatability mode. |
|
| Back to top |
|
|
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| dick scherrer wrote: |
Hello,
MERGE is valid in DB2 Z/os.
This may help - the MERGE info from the db2 v 9 manual:
Nowhere that i have a logon has v9 yet. . .
I don't know if MERGE will work if your system is still in compatability mode. |
I do understand it is valid. Like I mentioned, it works in the following format. The place I work has the db2 in the NFM
merge into tab1
using values (?,?) etc..
However , I only get the error when I use it the way that I mentioned it in the first post. That is the reason I wanted to know if this particular format is valid in z/os. The manual does seem to say it is valid... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| Yes, I do have that.. Thanks for attempting to help though.. Appreciate it. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| according to the manual : subselect as source table is not implemented |
|
| Back to top |
|
|
|
|
