Update difficulty

[cberthu]

Hi all,

I am wondering, when I issue this sql:

SELECT
CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR)
END AS NS
FROM D1.TCEOSURROGAT

I get exactly what I want in a NS Column:
NS
------------
WEB
%
_0ARS1
GCMS001
but when I try to updtae this result into the same table in a column
UPDSURRUSR:

UPDATE D1.TCEOSURROGAT TCEOSURROGAT
SET UPDSURRUSR = (
SELECT
CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR)
END AS NS
FROM D1.TCEOSURROGAT)

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000
I just don't know what is wrong... but like I wrote a lot of time I am
not a specialist.... but I am learning every day some more...

Thaks for your help

[Lennart]

cb*****@gmail.com wrote:

Hi all,

I am wondering, when I issue this sql:

SELECT
CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR)
END AS NS
FROM D1.TCEOSURROGAT

I get exactly what I want in a NS Column:
NS
------------
WEB
%
_0ARS1
GCMS001
but when I try to updtae this result into the same table in a column
UPDSURRUSR:

UPDATE D1.TCEOSURROGAT TCEOSURROGAT
SET UPDSURRUSR = (
SELECT
CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR)
END AS NS
FROM D1.TCEOSURROGAT)

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000
I just don't know what is wrong... but like I wrote a lot of time I am
not a specialist.... but I am learning every day some more...

Thaks for your help

The error means that you are trying to assign a set of values to the column:

I get exactly what I want in a NS Column:
NS
------------
WEB
%
_0ARS1
GCMS001

but you need a scalar value ( a single value, not a set of values ). You
can solve this by adding a where clause in the subselect as:

UPDATE D1.TCEOSURROGAT TCEOSURROGAT_1
SET UPDSURRUSR = (
SELECT
CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0 THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR)
END AS NS
FROM D1.TCEOSURROGAT TCEOSURROGAT_2
WHERE TCEOSURROGAT_1.<p.k= TCEOSURROGAT_2.<p.k>
)

Since I dont know what column(s) that is the key in D1.TCEOSURROGAT I
used <p.kas a marker

HTH
/Lennart

[Serge Rielau]

Maybe this is what you are trying to do?
UPDATE D1.TCEOSURROGAT
SET UPDSURRUSR
= CASE
WHEN LOCATE('*',STRIP(SURRUSR)) <0
THEN REPLACE(SURRUSR,'*','%')
WHEN LOCATE('%',STRIP(SURRUSR)) <0
THEN REPLACE(SURRUSR,'%','_')
ELSE STRIP(SURRUSR) END


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab