Headers in the unload-file

[Klaus Nolte]

Hi, everybody

the sql-statement

unload to '/tmp/stuff.txt' delimiter ';'
select fname, lname
from stuff;

will create a file which might look like this:

Peter;Winston;
Mike;Wiliam;
...

Is there any trick to get an additional header line into the file?
(with standard sql! no 4ql, shell-programming, awk ...)

First-Name;Last-Name;
Peter;Winston;
Mike;Wiliam;
...

Bye ... Klaus =B-)

Klaus Nolte | Living on earth may be expensive ...
kno...@asgard.han.de | ... but it includes an anual free trip around the
sun!

[Jonathan Leffler]

Klaus Nolte wrote:
> the sql-statement
>
> unload to '/tmp/stuff.txt' delimiter ';'
> select fname, lname
> from stuff;
>
> will create a file which might look like this:
>
> Peter;Winston;
> Mike;Wiliam;
> ...
>
> Is there any trick to get an additional header line into the file?
> (with standard sql! no 4ql, shell-programming, awk ...)
>
> First-Name;Last-Name;
> Peter;Winston;
> Mike;Wiliam;
> ...

SQLCMD from the IIUG archives has this facility, and also the types
of each column. I don't know of any other tool that does, but I don't
know about every other possible tool.

You can get a limited set of info out of the OUTPUT command of ISQL and
DB-Access; on the other hand, it does not produce a load format file.

--
Jonathan Leffler (jlef...@informix.com, jlef...@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>

[Steve Wright]

In article <rfq6o7...@P002899.ikknds>, Klaus Nolte
<kno...@asgard.han.de> writes
>Hi, everybody

>
>the sql-statement
>
>unload to '/tmp/stuff.txt' delimiter ';'
>select fname, lname
>from stuff;
>
>will create a file which might look like this:
>
>Peter;Winston;
>Mike;Wiliam;
>...
>
>Is there any trick to get an additional header line into the file?
>(with standard sql! no 4ql, shell-programming, awk ...)
>
>First-Name;Last-Name;
>Peter;Winston;
>Mike;Wiliam;
>...

How's about the following

SELECT "First-Name", "Last-Name"
FROM table1
UNION
SELECT fname, lname
FROM stuff

Where table1 is any table which has exactly one row.
--
Steve Wright

[Klaus Nolte]

In some of your last articles you wrote

>>Is there any trick to get an additional header line into the file?
>>(with standard sql! no 4ql, shell-programming, awk ...)
>>
>>First-Name;Last-Name;
>>Peter;Winston;
>>Mike;Wiliam;
>>...
>How's about the following
>
> SELECT "First-Name", "Last-Name"
> FROM table1
> UNION
> SELECT fname, lname
> FROM stuff
>
>Where table1 is any table which has exactly one row.

It works fine when all colums are type 'char'. If you have mixed types in
the two tables, this error comes up:

308: Corresponding column types must be compatible for each UNION
statement.

It seems to work, when the second table is inserted into the first table.
Here is my test script.

-------
create temp table stuff
(
fname char(30),
lname char(30),
pid int
) with no log;

insert into stuff values('Peter', 'Winston', 21322);
insert into stuff values('Mike', 'Wiliam', 34212);

create temp table header
(
header1 char(30),
header2 char(30),
header3 char(30)
) with no log;

insert into header values ('First-Name', 'Last-Name', 'Pers.-Id');

-- This statement doesn' t work:
-- 308: Corresponding column types must be compatible for each
-- UNION statement.

unload to '/tmp/stuff.txt' delimiter ';'
select header1, header2, header3
from header
union
select fname, lname, pid
from stuff
;

-- This construction seems to work :-)
-- Informix does the casting from 'int' to 'char'
insert into header
select *
from stuff
;

unload to '/tmp/stuff.txt' delimiter ';'
select *
from header
;
--------

The file looks like it should be.
Thanks for your help.

Bye... Klaus =B-)

--