Re: temp tables and serial columns

On Mon, 12 Mar 2007 10:10:23 -0400, "Art S. Kagel" <kagel@bloomberg.net> wrote: >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. > Does that count for . . . . select * from some_table into temp some_temp_table with no log . . . . JWC

John Carlson wrote: > On Mon, 12 Mar 2007 10:10:23 -0400, "Art S. Kagel" > <kagel@bloomberg.net> wrote: <SNIP> >> >>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. >> > > > Does that count for . . . . > > select * from some_table > into temp some_temp_table with no log The only way I MIGHT let that pass is if it were followed exclusively by selects from the temp table that included an explicit projection clause. Someone could make a case there that the sequence was as safe as an explicite projection clause in the SELECT ... INTO TEMP ..., but I'd press hard to change it just because there's no guarantee that someone latter won't add a SELECT * FROM some_temp_table, sneak it past a review by someoe less pedantic, and cause the whole thing to break a year later when we add a column to the source table. Art S. Kagel