I UNLOAD from a temp table with over 80 columns. Now I need to exclude the last column. Today I write:

UNLOAD select * from mytable TO 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';

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 sa_describe_query for that? We use SA16.

asked 02 Mar '16, 14:38

Rolle's gravatar image

Rolle
558495161
accept rate: 0%

edited 02 Mar '16, 14:51

This will not work...

SELECT list(sa_describe_query('select name from #mytable where name not in('exclude_column') )) FROM from #mytable
(02 Mar '16, 15:38) Rolle
Replies hidden

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

(03 Mar '16, 03:36) Justin Willey

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.

(03 Mar '16, 04:19) Volker Barth

I'm not sure what you mean. Can you show an example of how to do?

(03 Mar '16, 04:25) Rolle
Replies hidden
3

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

CREATE VARIABLE String1 LONG VARCHAR;
SET String1 = string('select',(select list(name) from sys.syscolumn where table_name = 'mytable' and column_name != 'exclude_column'),' from mytable');
EXECUTE IMMEDIATE String1;

Just check that string1 contains a valid SQL Statement

(03 Mar '16, 04:39) Justin Willey

I'm sorry, but I do not get your example to work. Can you clarify a bit? table_name not found?

(03 Mar '16, 11:30) Rolle
Replies hidden
1

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

CREATE VARIABLE 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 = 'mytable'
            and column_name != 'exclude_column'),
   ' from mytable');
EXECUTE IMMEDIATE String1;

Apparently, you will have to add the UNLOAD ... TO syntax around the SELECT statement to get the desired unload statement.

(03 Mar '16, 11:52) Volker Barth

Thanks Volker - that what comes of trying to do stuff on a phone :)

(03 Mar '16, 12:00) Justin Willey

Thanks!

I've got another problem. I wanna sort on the column I wanna exclude before export. Is it entirely possible? Like this:

CREATE VARIABLE 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 = 'mytable'
            and column_name != 'exclude_column'),
   ' from mytable order by exclude_column');
EXECUTE IMMEDIATE String1;
(03 Mar '16, 13:28) Rolle
Replies hidden

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:

 select FirstName, LastName from People order by DateOfBirth desc

Just remember that in your SET statement you are building up the SQL that you then want to execute.

(03 Mar '16, 13:36) Justin Willey

Very good! And I can use in EXECUTE IMMEDIATE in a function also?

(03 Mar '16, 13:43) Rolle
Replies hidden
(03 Mar '16, 13:54) Justin Willey

I can't get it work in a function. What am I doing wrong?

BEGIN DECLARE String1 LONG VARCHAR;

   EXECUTE IMMEDIATE
    'SET String1 =
       string(''select '',
          (select list(column_name order by column_id)
             from sys.syscolumn key join sys.systab
             where table_name = ''mytable''
                and column_name != ''exclude_column''),
       '' from mytable order by exclude_column'')';
   SELECT String1
END
(04 Mar '16, 05:15) Rolle

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

 EXECUTE IMMEDIATE String1;

So, in your last sample you are messing up the normal SQL statements and the EXECUTE IMMEDIATE statement.

(04 Mar '16, 06:03) Volker Barth

Thanks again! Justin's example doesn't work in my function. Error on line 1 near from?

(04 Mar '16, 07:55) Rolle

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

from Thanks!

I've got another problem....

There you got the order correct, in contrast to the later sample, where you put the SET statement within EXECUTE IMMEDIATE...

(04 Mar '16, 08:29) Volker Barth

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

(05 Mar '16, 18:13) Rolle
More comments hidden
showing 5 of 17 show all flat view

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.)

permanent link

answered 05 Mar '16, 19:12

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

edited 06 Mar '16, 15:45

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?

CREATE OR REPLACE FUNCTION DumpStuff()
RETURNS LONG VARCHAR
DETERMINISTIC
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');
  BEGIN
    EXECUTE IMMEDIATE WITH RESULT SET ON String1;
  END;
END;
(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 02 Mar '16, 14:38

question was seen: 1,685 times

last updated: 06 Mar '16, 15:45