creating data files from a query

[MizzGail]

I have written a query and pulling data from an informix table into an output file. I figured out how to strip the column labels off,,,
Now I have two questions
1. I have two blank lines on the beginning of the file that I want to get rid of. can i do this with an sql statement? (I know i can do it at the unix level)
2. My data is coming out in columns that are evenly spaces apart. I want to eliminate the space in between and have one field start after another, like a data file, a fixed format record with field1field2field3

thanks!

 

[olded]

MizzGail:

Unfortunately, the output capabilities of SQL is limiting. I think you'll have use external unix commands to solve your problem:

1) sed /^S/d # to delete blank lines

2) you can use awk's printf to handle the fixed length printing. If you need help, post a data sample and requirements.

Regards,

Ed

 

[vpshriyan]

MizzGail,

You may use the following syntax of UNLOAD for your purpose:

UNLOAD TO "cust.unl" DELIMITER "|" SELECT * FROM customer;

The dump file will be a data file; does not contain the field labels and the fileds delimited by pipe symbols contains no blank spaces in between.

Regards,
Shriyan

 

[MizzGail]

I have tried both formats and I'm getting closer. I suspect I will have to finish in unix, but just incase.. I thought I'd post my findings here.

This query:

unload to rcgweb08.out delimiter " "
select t_cpls, t_item, t_qanp, t_pric
from ttdsls032101
where t_stdt between today-1 and today

Produces this format:
T1 20-22-453 99999999.0 39.58
TTC 20-22-453 99999999.0 39.58
TTM 20-22-453 99999999.0 59.37


This query:
output to rcgweb08.out without headings
select t_cpls, t_item, t_qanp, t_pric
from ttdsls032101
where t_stdt between today-1 and today

Produces this format:
T1 34-60068-20 99999999.00000 1.900000000000
TTC 34-60068-20 99999999.00000 1.900000000000
TTM 34-60068-20 99999999.00000 2.850000000000



I need the output to look like this:
T1 20-22-453 99999999 39.5800
TTC20-22-453 99999999 39.5800
TTM20-22-453 99999999 59.3700

Any idea?
I thought of creating an internal table, but thought I might have the same issues pulling from any table.

 

[olded]

Hi:

How about using awk's printf function? It's similar to the "C" version
printf function:

Given d.file:

T1 20-22-453 99999999.21 39.58
TTC 20-22-453 99999999.34 39.58
TTM 20-22-453 99999999.67 59.37

The following awk script:

awk '
{
printf("%-3s%.8s %8d %7.4f\n", $1, $2, $3, $4)
} ' d.file

yields:

T1 20-22-45 99999999 39.5800
TTC20-22-45 99999999 39.5800
TTM20-22-45 99999999 59.3700

Let me know if you need help tweaking your awk script.

Regards,

Ed