Hi all,

DB2 V9.5 Fp5 LUW.

CREATE TABLE "CLT "."TBL_COLLECT_SW_LNX" (
"MACHINE_ID" VARCHAR(24) NOT NULL ,
"SW_ID" DECIMAL(8,0) NOT NULL ,
"LOCALTIME_INSERT" TIMESTAMP NOT NULL ,
"VERSION" VARCHAR(64) NOT NULL ,
"RELEASE" VARCHAR(48) NOT NULL ,
"SUMMARY" VARCHAR(400) ,
"VENDOR" VARCHAR(128) )
IN "ASSET" INDEX IN "ASSETIDX" ;


-- DDL Statements for primary key on Table "CLT
"."TBL_COLLECT_SW_LNX"

ALTER TABLE "CLT "."TBL_COLLECT_SW_LNX"
ADD CONSTRAINT "CONST_PK" PRIMARY KEY
("MACHINE_ID",
"SW_ID",
"VERSION",
"RELEASE");



Check out this statement:

MERGE INTO CLT.TBL_COLLECT_SW_LNX AS S USING
(
SELECT
A.MACHINE_ID,
A.SW_ID,
A.LOCALTIME_INSERT,
A.SW_NAME,
A.VERSION,
A.RELEASE,
A.SUMMARY,
A.VENDOR
FROM
(
SELECT
TU.MACHINE_ID,
TU.LOCALTIME_INSERT,
I.SW_NAME,
TU.VERSION,
TU.RELEASE,
TU.SUMMARY,
TU.VENDOR ,
ROWNUMBER() OVER ( PARTITION BY TU.MACHINE_ID, I.SW_ID,
TU.VERSION, TU.RELEASE ) AS RN
FROM
( VALUES ('001E4F3C7CE7000009AE5138', '2011-04-12-16.07.00',
'teste', '1.0.3', '4.el5_2', 'Libraries for applications using bzip2',
'Red Hat, Inc.'), ('001E4F3C7CE7000009AE5138', '2011-04-12-16.07.02',
'teste2', '1.0.3', '4.el5_2', 'Libraries for applications using
bzip2', 'Red Hat, Inc.') ) as TU

( MACHINE_ID, LOCALTIME_INSERT, SW_NAME, VERSION, RELEASE,
SUMMARY, VENDOR ) inner join CLT.TBL_COLLECT_SW_ID I ON I.SW_NAME =
TU.SW_NAME ) as A
( TU.MACHINE_ID, I.SW_ID, TU.LOCALTIME_INSERT, TU.SW_NAME,
TU.VERSION, TU.RELEASE, TU.SUMMARY, TU.VENDOR ) where rn = 1 ) INDATA
( MACHINE_ID, SW_ID, LOCALTIME_INSERT, SW_NAME, VERSION,
RELEASE, SUMMARY, VENDOR ) ON ( INDATA.MACHINE_ID=S.MACHINE_ID AND
INDATA.SW_ID=S.SW_ID AND INDATA.VERSION=S.VERSION AND
INDATA.RELEASE=S.RELEASE )

WHEN NOT MATCHED THEN INSERT
( MACHINE_ID, SW_ID, LOCALTIME_INSERT, VERSION, RELEASE,
SUMMARY, VENDOR ) VALUES ( INDATA.MACHINE_ID, INDATA.SW_ID,
INDATA.LOCALTIME_INSERT, INDATA.VERSION, INDATA.RELEASE,
INDATA.SUMMARY, INDATA.VENDOR ) WITH UR;


It returns the following error:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0108N The name "MACHINE_ID" has the wrong number of qualifiers.
SQLSTATE=42601

Someone has idea how I can resolve this error?

Thanks in advance.

Bruno.

Impression after just looking.....
qualifier for the clause were not allowed.

as A
( TU.MACHINE_ID, I.SW_ID, TU.LOCALTIME_INSERT, TU.SW_NAME,
TU.VERSION, TU.RELEASE, TU.SUMMARY, TU.VENDOR )

On May 11, 10:57*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

Because, you named all columns in the corresponding select list
and you made (at least) two mistakes.
First is that the order of I.SW_ID and TU.LOCALTIME_INSERT was
different from in the order of select list(if it was not
intentionaly).
Second is that the number of columns was different from the number of
columns in the select list(RN was dropped).

Another isuue what I realized was that
you had better to add ORDER BY clause to ROWNUMBER() OVER (...),
if you don't like to select arbitrally a row to be compared.

On May 11, 11:22*am, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:
Tonkuma,

Thanks for your help.
Now, its works fine.

Regards.

Bruno.