Informix - Display Column Header

[rmueller58]

I need to send output in an informix script to a text file, I need to output the column header at the top of the file.. Can someone point me in a direction to look?


unload to /tmp/staff_upload.txt delimiter ','
select T1.empl_no ReferenceCode, T1.f_name FirstName,T1.l_name LastName,
T2.desc_x Group, T3.home_phone HomePhone,
T1.email_addr EmailAddress, T1.base_loc Institution
from employee T1, person T3 LEFT OUTER JOIN employee_type T2 on
T3.empl_type=T2.code
where T3.empl_no=T1.empl_no and T3.term_date is null and T1.base_loc<>99 and
T2.desc_x not in ('O', 'S', 'U', 'V') and T3.status='A ' and
T1.home_orgn in ('A ', 'B ', 'C ', 'E ',
'N ', 'T ')

[Peter Godron · Accepted solution]

Rex,
there are some examples in
google groups:comp.databases.informix

http://groups.google.co.uk/group/comp.databases.informix/browse_thread/thread/515c606066f3655a/c6613032d83a8be0%23c6613032d83a8be0?sa=X&oi=groupsr&start=0&num=3

http://groups.google.co.uk/group/comp.databases.informix/browse_thread/thread/b6efa49f4a5a963/b832a81469c86e02%23b832a81469c86e02?sa=X&oi=groupsr&start=1&num=3

[Steve Lewis]

The sql unload command was never designed for reports. You should really use a report generator such as isql ace or something proper for this.

What you can do to get the column headings at the top, is to replace 'unload' with 'output', then also select constants inbetween column names. The constant to use is ";"

output to "path_to_filename"
select col1,";" , col2 ...

The problem with that is that you get (constant) output between the columns names.

You will have to be careful with page width because it has a tendancy to list column names on the left with values on the right.

[rmueller58]

Thanks guys, we will try it.

[klahr_1]

This question is really for Steve Lewis, but possibly you may have an answer or if not, please forward to Steve Lewis or send me his address. That would be appreciated.

Steve suggested using 'output' instead of 'unload' but warned to be careful about page size to avoid having the date displayed vertically, column-names on the left and data values on the left. Is there a way to control the page width, so that a wide line can be displayed horizontally in a unix 'output' file in Informix?

Thanks

[rmueller58]

I did something a bit different.

I created a header file and a data file,

I append the two together and get the results I need

unload to mpsapphire.txt
select id, empl_no, hire_date from applicant where
hire_date > '01/01/2006' and empl_no is not null
unload to "/tmp/mps_connected.csv" delimiter ','
select T1.empl_no ReferenceCode, T1.f_name FirstName,
T1.l_name LastName, T3.home_phone HomePhone,
T1.email_addr EmailAddress, T3.term_date Terminate,
Case when T1.base_loc=1 then '280017000'
when T1.base_loc=2 then '280017998'
when T1.base_loc=3 then '280017999'
when T1.base_loc=10 then '280017002'
when T1.base_loc=12 then '280017002'
else '280017'||T4.slocation end Institution,
case when T3.empl_type='A' then 'Admin'
when T3.empl_type='C' then 'Faculty'
when T3.empl_type='E' then 'Faculty'
else 'Staff' end ContactType,
case when T1.base_loc=2 then 'Support'
when T1.base_loc=3 then 'Support'
when T1.base_loc=10 then 'Support'
ELSE NULL end Group
from employee T1, person T3, OUTER(employee_type T2),
OUTER ('informix'.neprs_loc T4)
where T3.empl_type=T2.code and T4.location=T1.base_loc and
T3.empl_no=T1.empl_no and
((T3.empl_type not in ('R', 'S', 'T') and
T3.status like 'ACT%') or
T3.term_date>(TODAY-30))

[Raghuschandra]

Hi,

Check this link also.
http://groups.google.co.uk/group/comp.databases.informix/browse_frm/thread/b6efa49f4a5a963/b832a81469c86e02?lnk=st&q=&rnum=4&hl=en#b832a81469c86e02

We can obtain the existing column names and then load it to the temp table and then unload it.

select colname from systables a, syscolumns b where tabname = "ourtable" and a.tabid=b.tabid into temp tmp_tab with no log;

insert into tmp_tab
select * from ourtable;

unload to /tmp/data.txt select * from tmp_tab

HTH