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 asked 02 Mar '16, 14:38 Rolle |
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; example: 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.) answered 05 Mar '16, 19:12 Justin Willey Thanks again. That's my problem. As I wrote earlier I do not get it to work in a Function. In a procedure it works without problems. What is the difference in a Function?
(06 Mar '16, 05:06)
Rolle
Replies hidden
The table I read from is a temporary table...
(06 Mar '16, 06:40)
Rolle
Replies hidden
The difference is that a stored function is not expected to return a result set but to return a return value, i.e. you can't do a final "SELECT * FROM ..." in a stored function (*), neither from a normal SELECT statement nor one dynamically run with EXECUTE IMMEDIATE. However, as your original intent was to use an "UNLOAD SELECT..." AFAIK, that should work within a stored function as it does not return a result set but just "does some work". A stored function requires a RETURN statement to return some value, say, the number of exported rows in the case of an unload. (*) Exception: It is possible to use a "SELECT ... INTO myVariable ..." to fill a variable with a desired return value or the like.
(06 Mar '16, 06:59)
Volker Barth
What kind of temporary table is that, i.e. how is is declared - CREATE GLOBAL TEMPORARY TABLE, DECLARE LOCAL TEMPORARY TABLE, CREATE LOCAL TEMPORARY TABLE, CREATE TABLE #...? Not every kind of them is stored in the system catalog, and only for those that are you can select the columns names based on Justin's query. I.e. Justin's addition to use a temporary table declared with SELECT INTO LOCAL TEMPORARY TABLE will create a temp. table not contained in the system catalog. Here, it would be either necessary to create a global temp. table or to use the "sa_describe_query" procedure (as mentioned in the previous FAQ) with a "select * from MyLocalTempTable" to get the column names.
(06 Mar '16, 07:07)
Volker Barth
We use SELECT ... INTO LOCAL TEMPORARY TABLE
(06 Mar '16, 07:11)
Rolle
|
This will not work...
You would have to use EXECUTE IMMEDIATE if you want to build the SQL dynamically like that - see http://dcx.sap.com/index.html#sa160/en/dbreference/execute-immediate-statement.html
Just to add to Justin's answer to possibly clarify a misunderstanding:
sa_describe_query() does return information about the query's result set. It does not return the result set itself. That's why you will need to use the result of sa_describe_query() to then build "the real query" dynamically.
In case you just need a fixed select list (i.e. the schema of mytable remains the same for different calls) but you don't want to type it yourself (which is understandable of course), you can simply run your query within DBISQL and copy the result to build the query text itself.
I'm not sure what you mean. Can you show an example of how to do?
With EXECUTE IMMEDIATE you can build a SQL string and then execute the SQL - so (and this is untested as I don't have access to a database here) you first need a statement that builds the string you want to execute and that is what you execute with EXECUTE IMMEDIATE eg
Just check that string1 contains a valid SQL Statement
I'm sorry, but I do not get your example to work. Can you clarify a bit? table_name not found?
As Justin has stated, it was untested - the sys.syscolumn system table has to be joined against the sys.systab system table to filter by a table name, such as
Apparently, you will have to add the UNLOAD ... TO syntax around the SELECT statement to get the desired unload statement.
Thanks Volker - that what comes of trying to do stuff on a phone :)
Thanks!
I've got another problem. I wanna sort on the column I wanna exclude before export. Is it entirely possible? Like this:
It shouldn't be a problem, fields in the ORDER BY clause don't need to be mentioned in the SELECT list - just think of a simple example:
Just remember that in your SET statement you are building up the SQL that you then want to execute.
Very good! And I can use in
EXECUTE IMMEDIATE
in a function also?Yes, but there are things to consider - look at http://dcx.sap.com/index.html#sa160/en/dbreference/execute-immediate-statement.html
I can't get it work in a function. What am I doing wrong?
BEGIN DECLARE String1 LONG VARCHAR;
Confine Justin's and my samples:
The SET statement is used to build the SELECT statement as a string. That's just a normal SET statement and does not require EXECUTE IMMEDIATE.
In contrast, EXECUTE IMMEDIATE is then used with the string variable as parameter, such as
So, in your last sample you are messing up the normal SQL statements and the EXECUTE IMMEDIATE statement.
Thanks again! Justin's example doesn't work in my function. Error on line 1 near from?
Sigh. Yes, that was already stated here, see my comments from about 20 hours ago. I just wanted to hint at the general order of the statements - confine your own attempt introduced with
There you got the order correct, in contrast to the later sample, where you put the SET statement within EXECUTE IMMEDIATE...
Maybe I'm an idiot. But I do not get this to work in my function. receive error message: syntax error near 'from' on line 1? I really need help here...