temp tables and serial columns
Problem: in IDS 9.4, "SELECT * ... INTO TEMP" preserves a source column's SERIAL type in the temp table, so you can't set the id to 0 and re-insert to generate a new key, nor alter the temp column to plain INTEGER; the poster asked whether IDS 10/11 offered an option to suppress this. Replies gave workarounds rather than a product feature: cast the column in the select list (serial_column::INT) with all columns listed explicitly, or create a view over the table with the serial cast to integer and select * from that view into temp. Art Kagel also argued explicit column lists are good practice, or generate them with an awk/Perl script.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are on IDS 9.4 . One thing that has always annoyed me about serial columns is that they become serial instead of integer when you do a "select into temp x" . If you are trying to duplicate a row for a customer, part, etc, except for the id , you cannot update the temp to change the id to 0 and do an insert back into the table to force a new id to be generated. Neither can you alter the temp table to make the column a plain old integer. You have to create table blah ( ...) with an integer where the serial was and go from there. That is a bit of a hassle if there are many columns. Is there any relief for this issue in IDS 10 or IDS 11 ? Like "into temp t with no serial" or something like that?
Bill64bits wrote: > We are on IDS 9.4 . > One thing that has always annoyed me about serial columns is that they > become serial instead of integer > when you do a "select into temp x" . > If you are trying to duplicate a row for a customer, part, etc, except > for the id , you cannot update the temp > to change the id to 0 and do an insert back into the table to force a > new id to be generated. > Neither can you alter the temp table to make the column a plain old integer. > You have to create table blah ( ...) with an integer where the serial > was and go from there. > That is a bit of a hassle if there are many columns. > > Is there any relief for this issue in IDS 10 or IDS 11 ? > Like "into temp t with no serial" or something like that? select INT::serial_column, .... INTO TEMP fred; Art S. Kagel
"fred" <fred@bloomberg.com> wrote in message news:45F1B16C.7020105@bloomberg.com... > select INT::serial_column, .... > INTO TEMP fred; > > Art S. Kagel > So, if there are 100 columns in the table and I want them all in the temp, I must list all 100 columns in the select (instead of *) so that I can cast one column to INT ? That seems lame. I guess IBM does not cater to lazy programmers or dba's. :-)
Bill64bits wrote:
> We are on IDS 9.4 .
> One thing that has always annoyed me about serial columns is that they
> become serial instead of integer
> when you do a "select into temp x" .
> If you are trying to duplicate a row for a customer, part, etc, except
> for the id , you cannot update the temp
> to change the id to 0 and do an insert back into the table to force a
> new id to be generated.
> Neither can you alter the temp table to make the column a plain old integer.
> You have to create table blah ( ...) with an integer where the serial
> was and go from there.
> That is a bit of a hassle if there are many columns.
>
> Is there any relief for this issue in IDS 10 or IDS 11 ?
> Like "into temp t with no serial" or something like that?
How about creating a view on the table which has the serial cast to an
integer??
create table t1 ( c1 serial, c2 char(20));
insert into t1 select 0, tabname from systables;
create view t1_view(c1,c2) as select c1::integer as c1, c2 from t1;
select * from t1_view into temp tmp1;
update tmp1 set c1=5;
Just an idea,
John
Bill64bits wrote: > > "fred" <fred@bloomberg.com <mailto:fred@bloomberg.com>> wrote in message > news:45F1B16C.7020105@bloomberg.com... > > select INT::serial_column, .... > > INTO TEMP fred; > > > > Art S. Kagel > > > > > So, if there are 100 columns in the table and I want them all in the > temp, I must list all 100 columns in the select > (instead of *) so that I can cast one column to INT ? Yes. > That seems lame. Not lame. Just not for the superficially lazy. To be TRULY lazy (and all programmers should be TRULY lazy) you will always use a column list anyway because it saves one from having to modify code to take account of schema changes that do not affect a particular module and it simplified rollout/rollback procedures. > I guess IBM does not cater to lazy programmers or dba's. :-) I reprimand ANYONE here who writes SELECT * or INSERT INTO tablename VALUES ... (ie without a column list). Such queries are doomed to blow up when the schema has to be changed to add or drop a column and our coding standard makes such SQL an automatic code review failure. If you really can't be bothered to type those 100 column names, take the Clown's suggestion and write an AWK or Perl script to generate the column list for you. Art S. Kagel
John Miller wrote:
> Bill64bits wrote:
>
>> We are on IDS 9.4 .
>> One thing that has always annoyed me about serial columns is that they
>> become serial instead of integer
>> when you do a "select into temp x" .
>> If you are trying to duplicate a row for a customer, part, etc, except
>> for the id , you cannot update the temp
>> to change the id to 0 and do an insert back into the table to force a
>> new id to be generated.
>> Neither can you alter the temp table to make the column a plain old
>> integer.
>> You have to create table blah ( ...) with an integer where the serial
>> was and go from there.
>> That is a bit of a hassle if there are many columns.
>>
>> Is there any relief for this issue in IDS 10 or IDS 11 ?
>> Like "into temp t with no serial" or something like that?
>
>
>
> How about creating a view on the table which has the serial cast to an
> integer??
>
>
> create table t1 ( c1 serial, c2 char(20));
> insert into t1 select 0, tabname from systables;
> create view t1_view(c1,c2) as select c1::integer as c1, c2 from t1;
> select * from t1_view into temp tmp1;
> update tmp1 set c1=5;
My guess is he couldn't be bothered to type all that. He already stated
that it's too much work to define the temp table first or to use a cast in
the select...into temp...
Art S. Kagel