467,887 Members | 1,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

Update difficulty

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
  • viewed: 2333
Share:
2 Replies
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
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.

Similar topics

198 posts views Thread by Michael N. Christoff | last post: by
2 posts views Thread by Dejan Pujic | last post: by
reply views Thread by Doug | last post: by
6 posts views Thread by Matt | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.