I UNLOAD from a temp table with over 80 columns. Now I need to exclude the last column. Today I write:
The last column is the sorting column and need to be in the temp table.
Instead of write all columns, is there a smart way to exclude the last column with select? Can I use
You just need to read the docs - there is a very similar example given here. This is the way you need to organise your procedure - as Volker said you have to separate the construction of the query string from its execution:
CREATE PROCEDURE DumpStuff() BEGIN DECLARE String1 LONG VARCHAR; SET String1 = string('select ',(select list(column_name order by column_id) from sys.syscolumn key join sys.systab where table_name = 'Stuff' and column_name != 'Field2'),' from Stuff order by Field2;'); EXECUTE IMMEDIATE WITH RESULT SET ON String1; END;
create table Stuff (StuffID int default autoincrement, Field1 char(10), Field2 char(10), Field3 char(10)); insert into Stuff(Field1,Field2,Field3) values('aa','bb','cc'); insert into Stuff(Field1,Field2,Field3) values('xx','yy','zz'); insert into Stuff(Field1,Field2,Field3) values('gg','hh','ii'); commit; call DumpStuff()gives you:
StuffID,Field1,Field3 1,'aa','cc' 3,'gg','ii' 2,'xx','zz'However from the look of your other question, you want to unload this in the procedure, so I suggest you adapt my procedure to fill a temporary table and then unload the temporary table. Use an INTO LOCAL TEMPORARY TABLE clause in the select statement you build in the procedure.(NB in that case you would drop the bit about WITH RESULT SET ON as your procedure would not be returning a result set to the calling environment.)