By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Update difficulty

P: n/a
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

Jul 18 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jul 18 '07 #2

P: n/a
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
Jul 19 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.