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
