Column names in unload

hktke...@my-deja.com

unread,

Sep 19, 2000, 3:00:00 AM9/19/00

to

Hi there,

Is it possible to include the column names when I do a
UNLOAD TO....SELECT.... statement?

Thanks!

Kenneth

Sent via Deja.com http://www.deja.com/
Before you buy.

Colin McGrath

unread,

Sep 19, 2000, 3:00:00 AM9/19/00

to

UNLOAD TO....SELECT "col1", col1, "col2", col2, ....

--
Colin McGrath c...@trac3000.ueci.com
Washington Group International, Inc. (215) 422-4144
Philadelphia, PA, USA
Any opinions I state are my own and not necessarily those of my employer

Jonathan Leffler

unread,

Sep 19, 2000, 3:00:00 AM9/19/00

to

hktke...@my-deja.com wrote:
> Is it possible to include the column names when I do a
> UNLOAD TO....SELECT.... statement?

In the standard Informix products, no. The closest approach is:

OUTPUT TO "somefile" SELECT ...

However, that does not guarantee that you get UNLOAD format; you might
get the columns down the page format instead.

However, it just so happens that SQLCMD has options -H (for headings)
and -T (for types) which can be used in conjunction with UNLOAD to get
the column names (and/or types) into the unload file:

sqlcmd -d stores -HT 'unload to "junk" select * from customer'
sqlcmd -UHTd stores -t customer -o junk
sqlunload -HTd stores -t customer -o junk

--
Yours,
Jonathan Leffler (Jonathan...@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"

Colin McGrath

unread,

Sep 19, 2000, 3:00:00 AM9/19/00

to

With a few extra steps, if you know the column names you are selecting:

select "msg_type" msg_type,
"code " code,
"description " description
from systables
where tabid = 1
into temp tmp_tab with no log;

insert into tmp_tab
select msg_type, code, description
from trac_msg;

unload to tmp_tab.out
select *
from tmp_tab

You would get:

msg_type|code|description|
EGE|5001|There are no more rows in the direction you are going|
EGE|5002|There are no rows satisfying the conditions|
EGE|5003|There are no rows in the current list|
EGE|5004|The current row position contains a deleted row|

(The first record, the header field record, needs its literals to be padded
as long as the longest strings going into the table)

hktke...@my-deja.com wrote:
>
> Hi there,

>
> Is it possible to include the column names when I do a
> UNLOAD TO....SELECT.... statement?
>

Jacob Salomon

unread,

Sep 20, 2000, 3:00:00 AM9/20/00

to

Colin,

there is no need to pad the heading. All you need is to pipe the unl
file through my utility script, beautify-unl.sh; this will allign all
the columns very nicely.

In the IIUG library, it got entered as beautify_unl.sh - with an
underscore(_) instead of the dash(-) I had named it with.

-- Helpful Jake.

In article <8q8jcf$qfn$1...@news.xmission.com>,

--
+----- Jacob Salomon - DBA JSal...@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |