On 19 Apr 2012 07:17, rob@xxxxxxxxx wrote:
I have multiple rows where Column A is the same. I want to copy
those while changing the value of column A to some constant. For
example

Insert into mytable (ColA, ColB, ColC)
Values( Select 'NewConstant', ColB, ColC
from mytable
where ColA='OldValue'
)

But I am getting:
SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable "ANORDER@xxxxxxxxxxxxx"
not found. <<SNIP>>

Per the followup reply, this issue was already resolved by dropping the apparently unintended usage of the VALUES clause; i.e. leaving in place just the apparently intended specification of a /fullselect/ on INSERT.

An interesting result\error, the -206.

Notice the change\addition for "global variable" on the INSERT with VALUES clause:
_DB2 for i SQL:Inserting rows using the INSERT statement_
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafyinsert.htm
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyinsert.htm

I am curious if the value of ColB for the selected row was either the string 'ANORDER@xxxxxxxxxxxxx' or perhaps instead the string '"ANORDER@xxxxxxxxxxxxx"'? I understand how new function can lead to more bizarre effects in diagnosing an error [in this case with the addition of both "values-row" and "global variable" support], but that error is tough to figure out without knowing some actual data. The effect even seems possibly, to be unintended; i.e. should row data actually be [re]interpreted as a variable name?

Does the -206 recreate exactly the same with the following script, or might you provide a similarly simple script to show what does?:

create table qtemp/mytable
( ColA varchar(17), ColB varchar(65), ColC dec )
;
insert into qtemp/mytable values
('OldValue', 'ANORDER@xxxxxxxxxxxxx', 1)
;
insert into qtemp/mytable values
( select 'NewConstant', ColB, ColC
from qtemp/mytable
where ColA='OldValue'
)
;

If the data as replacement text for the SQL0206 was from ColB, I wonder what the effect would be if the string was multiple words\tokens separated by blanks or expressions or keywords; e.g. ColB as one of the strings: 'CURRENT USER', 'the end', 'CAST(COLC AS CHAR(20))', 'DEFAULT', or 'NULL'

Prior to /values-row/ support, the error would have been -412 SQL0412 "Subquery with more than one result column not valid." Had the intent really been to use a VALUES clause instead of a fullselect, then a possible resolution would be using [makes little sense, but the concept is what matters] scalar subselects:

insert into qtemp/mytable (ColA, ColB, ColC)
Values( (select 'NewConstant' from mytable where ColA='OldValue')
, (select ColB from mytable where ColA='OldValue')
, (select ColC from mytable where ColA='OldValue')
)

However that would not work if more than one row were selected, per -811 SQL0811 "Result of SELECT more than one row", because in that context a scalar subselect would be required.

I am not sure what if any error would occur for v6r1; i.e. with values-row support but without global-variable support.

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].