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
midrange.com
