dbaccess output display width from cron
A user running an SQL script through dbaccess from cron found output wrapped vertically (one column per line) instead of the wide horizontal layout seen interactively, because cron jobs have no controlling terminal and dbaccess assumes an 80-column display. Suggestions included shortening columns, Ace/ISQL reports, Perl, SQLCMD, and setting COLUMNS=132 (which the poster tried without success). He resolved it by UNLOADing to a file and formatting it with the IIUG beautify-unl.sh script plus tr/sed, also learning that SUM/COUNT results are DECIMAL, hence the trailing ".0".
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log in,
it seems to recognize that it's not a tty
Someone to correct me but this can be done, the way you're doing it, with
dbaccess.
Can think of 2 solutions:
1. Shorten the columns by casting or substringing ;)
2. Use unload to a temp file and reformat it with awk
Then email it to your users.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 12:05:34 -0400 (EDT)
Subject: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and mail
the results out to a few people.
When run from the command line, the output comes out with columns displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log in,
it seems to recognize that it's not a tty
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
You
could also use the Ace report writer...it has the ability to format
your output.
Joe V.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Tuesday, April 26, 2005 11:26 AM
To: ids@iiug.org
Subject: Re: dbaccess output display width from cron [4813]
Someone to correct me but this can be done, the way you're doing it,
with dbaccess.
Can think of 2 solutions:
1. Shorten the columns by casting or substringing ;)
2. Use unload to a temp file and reformat it with awk
Then email it to your users.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 12:05:34 -0400 (EDT)
Subject: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns
displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle
displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log
in,
it seems to recognize that it's not a tty
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
Output
formatting is one of the features of the Structured Query Scripting
Language. See http://www.4glworks.com/4wrcsqll.htm#FGWLANGREF for more goodies.
If that tickles your interest, get it from http://www.4glworks.com/misc.htm
Danny Wright wrote:
> I set up a little shell script to run an sql script via dbaccess and mail
> the results out to a few people.
>
> When run from the command line, the output comes out with columns displayed
> horizontally.
>
> it looks something like this:
>
> column1 column2 column3 etc.....
>
> data data data..........
>
> But when run from cron (or at), it displays like this:
>
> column1 data
> column2 data
> column3 data
>
>
>
> How can I convince it that my display is wide enough to handle displaying
> the data horizontally?
>
> Even though I'm setting my environment the same way I would when I log in,
> it seems to recognize that it's not a tty
>
>
>
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Informix faq http://www.iiug.org/techinfo/faq/informix.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Agree with Jean
Consider Perl report features.
Unload to file (unload to foo.bar select ....) then feed your perlreport script. If you need some example on this, just ask!
Or remove innecesary fields :-)
All the best.
-----Mensaje original-----
De: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] En
nombre de Danny Wright
Enviado el: Martes, 26 de Abril de 2005 01:06 p.m.
Para: ids@iiug.org
Asunto: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns
displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle
displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log
in,
it seems to recognize that it's not a tty
AVISO LEGAL:
Esta informacion es privada y confidencial y esta dirigida unicamente a su
destinatario. Si usted no es el destinatario original de este mensaje y por
este medio pudo acceder a dicha informacion por favor elimine el mensaje. La
distribucion o copia de este mensaje esta estrictamente prohibida. Esta
comunicacion es solo para propositos de informacion y no debe ser considerada
como propuesta, aceptacion ni como una declaracion de voluntad oficial de
REPSOL YPF S.A. y/o subsidiarias y/o afiliadas. La transmision de e-mails no
garantiza que el correo electronico sea seguro o libre de error. Por
consiguiente, no manifestamos que esta informacion sea completa o precisa.
Toda informacion esta sujeta a alterarse sin previo aviso.
This information is private and confidential and intended for the recipient
only. If you are not the intended recipient of this message you are hereby
notified that any review, dissemination, distribution or copying of this
message is strictly prohibited. This communication is for information purposes
only and shall not be regarded neither as a proposal, acceptance nor as a
statement of will or official statement from REPSOL YPF S.A. and/or
subsidiaries and/or affiliates. Email transmission cannot be guaranteed to be
secure or error-free. Therefore, we do not represent that this information is
complete or accurate and it should not be relied upon as such. All information
is subject to change without notice.
Is there a version of Ace for Informix 10?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Joe Vidrine
Sent: Tuesday, April 26, 2005 2:40 PM
To: ids@iiug.org
Subject: RE: dbaccess output display width from cron [4816]
You could also use the Ace report writer...it has the ability to format
your output.
Joe V.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Tuesday, April 26, 2005 11:26 AM
To: ids@iiug.org
Subject: Re: dbaccess output display width from cron [4813]
Someone to correct me but this can be done, the way you're doing it,
with dbaccess.
Can think of 2 solutions:
1. Shorten the columns by casting or substringing ;)
2. Use unload to a temp file and reformat it with awk
Then email it to your users.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 12:05:34 -0400 (EDT)
Subject: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns
displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle
displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log
in,
it seems to recognize that it's not a tty
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
Thanks for all the input from everyone.
I had already stripped out as much unnecessary stuff that I could think of
and so I went with Jean's 2nd suggestion - unloading to a file and
maniupulating that.
By sheer luck, I happened to notice a utility I had downloaded months ago
and forgotten about called beautify-unl.sh (I'm fairly certain it's from the
IIUG software depository).
beautify-unl.sh lines the columns up, but leaves the delimiter and doing an
unload added a decimal place to the SUM and COUNT aggregate functions in my
sql statement, so after unloading, I run this....
beautify-unl.sh <unload-file> | tr '|' '\\\\t' | sed "s/\\\\.0//g"
I'm a bit curious as to why doing an unload causes 1 decimal place to be
displayed, especially since I'm summing integers, and count should never
return anything but an integer, but that's trivial.
-----Original Message-----
From: ERIEZNIKR@REPSOLYPF.COM [mailto:ERIEZNIKR@REPSOLYPF.COM]
Sent: Tuesday, April 26, 2005 1:03 PM
To: dwright@sherwoodfoods.com; ids@iiug.org
Subject: RE: dbaccess output display width from cron [4812]
Agree with Jean
Consider Perl report features.
Unload to file (unload to foo.bar select ....) then feed your perl reportscript. If you need some example on this, just ask!
Or remove innecesary fields :-)
All the best.
-----Mensaje original-----
De: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] En nombre
de Danny Wright Enviado el: Martes, 26 de Abril de 2005 01:06 p.m.
Para: ids@iiug.org
Asunto: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and mail
the results out to a few people.
When run from the command line, the output comes out with columns displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log in,
it seems to recognize that it's not a tty
AVISO LEGAL:
Esta informacion es privada y confidencial y esta dirigida unicamente a su
destinatario. Si usted no es el destinatario original de este mensaje y por
este medio pudo acceder a dicha informacion por favor elimine el mensaje. La
distribucion o copia de este mensaje esta estrictamente prohibida. Esta
comunicacion es solo para propositos de informacion y no debe ser
considerada como propuesta, aceptacion ni como una declaracion de voluntad
oficial de REPSOL YPF S.A. y/o subsidiarias y/o afiliadas. La transmision de
e-mails no garantiza que el correo electronico sea seguro o libre de error.
Por consiguiente, no manifestamos que esta informacion sea completa o
precisa. Toda informacion esta sujeta a alterarse sin previo aviso.
This information is private and confidential and intended for the recipient
only. If you are not the intended recipient of this message you are hereby
notified that any review, dissemination, distribution or copying of this
message is strictly prohibited. This communication is for information
purposes only and shall not be regarded neither as a proposal, acceptance
nor as a statement of will or official statement from REPSOL YPF S.A. and/or
subsidiaries and/or affiliates. Email transmission cannot be guaranteed to
be secure or error-free. Therefore, we do not represent that this
information is complete or accurate and it should not be relied upon as
such. All information is subject to change without notice.
Ace is part of Informix-ISQL (If I'm not bad) so if you only install the
engine you won't have it.
I think isql 7.32 should works against ids 10, so should work too.
J.
-----Original Message-----
From: "Tomchik, Kate" <Kate_Tomchik@homedepot.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 16:18:36 -0400 (EDT)
Subject: RE: dbaccess output display width from cron [4821]
Is there a version of Ace for Informix 10?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Joe Vidrine
Sent: Tuesday, April 26, 2005 2:40 PM
To: ids@iiug.org
Subject: RE: dbaccess output display width from cron [4816]
You could also use the Ace report writer...it has the ability to format
your output.
Joe V.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Tuesday, April 26, 2005 11:26 AM
To: ids@iiug.org
Subject: Re: dbaccess output display width from cron [4813]
Someone to correct me but this can be done, the way you're doing it,
with dbaccess.
Can think of 2 solutions:
1. Shorten the columns by casting or substringing ;)
2. Use unload to a temp file and reformat it with awk
Then email it to your users.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 12:05:34 -0400 (EDT)
Subject: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns
displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle
displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log
in,
it seems to recognize that it's not a tty
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
beautify-unl.sh?
I'll look at it in the IIUG, it sounds like a helpfull utility.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 16:37:17 -0400 (EDT)
Subject: RE: dbaccess output display width from cron [4822]
Thanks for all the input from everyone.
I had already stripped out as much unnecessary stuff that I could think of
and so I went with Jean's 2nd suggestion - unloading to a file and
maniupulating that.
By sheer luck, I happened to notice a utility I had downloaded months ago
and forgotten about called beautify-unl.sh (I'm fairly certain it's from the
IIUG software depository).
beautify-unl.sh lines the columns up, but leaves the delimiter and doing an
unload added a decimal place to the SUM and COUNT aggregate functions in my
sql statement, so after unloading, I run this....
beautify-unl.sh <unload-file> | tr '|' '\\\\t' | sed "s/\\\\.0//g"
I'm a bit curious as to why doing an unload causes 1 decimal place to be
displayed, especially since I'm summing integers, and count should never
return anything but an integer, but that's trivial.
-----Original Message-----
From: ERIEZNIKR@REPSOLYPF.COM [mailto:ERIEZNIKR@REPSOLYPF.COM]
Sent: Tuesday, April 26, 2005 1:03 PM
To: dwright@sherwoodfoods.com; ids@iiug.org
Subject: RE: dbaccess output display width from cron [4812]
Agree with Jean
Consider Perl report features.
Unload to file (unload to foo.bar select ....) then feed your perl reportscript. If you need some example on this, just ask!
Or remove innecesary fields :-)
All the best.
-----Mensaje original-----
De: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] En nombre
de Danny Wright Enviado el: Martes, 26 de Abril de 2005 01:06 p.m.
Para: ids@iiug.org
Asunto: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and mail
the results out to a few people.
When run from the command line, the output comes out with columns displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log in,
it seems to recognize that it's not a tty
AVISO LEGAL:
Esta informacion es privada y confidencial y esta dirigida unicamente a su
destinatario. Si usted no es el destinatario original de este mensaje y por
este medio pudo acceder a dicha informacion por favor elimine el mensaje. La
distribucion o copia de este mensaje esta estrictamente prohibida. Esta
comunicacion es solo para propositos de informacion y no debe ser
considerada como propuesta, aceptacion ni como una declaracion de voluntad
oficial de REPSOL YPF S.A. y/o subsidiarias y/o afiliadas. La transmision de
e-mails no garantiza que el correo electronico sea seguro o libre de error.
Por consiguiente, no manifestamos que esta informacion sea completa o
precisa. Toda informacion esta sujeta a alterarse sin previo aviso.
This information is private and confidential and intended for the recipient
only. If you are not the intended recipient of this message you are hereby
notified that any review, dissemination, distribution or copying of this
message is strictly prohibited. This communication is for information
purposes only and shall not be regarded neither as a proposal, acceptance
nor as a statement of will or official statement from REPSOL YPF S.A. and/or
subsidiaries and/or affiliates. Email transmission cannot be guaranteed to
be secure or error-free. Therefore, we do not represent that this
information is complete or accurate and it should not be relied upon as
such. All information is subject to change without notice.
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
ISQL
7.32 should work fine with IDS 10.00. (If it doesn't, let us know -
it is most likely a bug somewhere...)
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
"Tomchik, Kate" <Kate_Tomchik@homedepot.com>
Sent by: forum.subscriber@iiug.org
04/26/2005 01:18 PM
To
ids@iiug.org
cc
Subject
RE: dbaccess output display width from cron [4821]
Is there a version of Ace for Informix 10?
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Joe Vidrine
Sent: Tuesday, April 26, 2005 2:40 PM
To: ids@iiug.org
Subject: RE: dbaccess output display width from cron [4816]
You could also use the Ace report writer...it has the ability to format
your output.
Joe V.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Jean Sagi
Sent: Tuesday, April 26, 2005 11:26 AM
To: ids@iiug.org
Subject: Re: dbaccess output display width from cron [4813]
Someone to correct me but this can be done, the way you're doing it,
with dbaccess.
Can think of 2 solutions:
1. Shorten the columns by casting or substringing ;)
2. Use unload to a temp file and reformat it with awk
Then email it to your users.
J.
-----Original Message-----
From: "Danny Wright" <dwright@sherwoodfoods.com>
To: ids@iiug.org
Date: Tue, 26 Apr 2005 12:05:34 -0400 (EDT)
Subject: dbaccess output display width from cron [4812]
I set up a little shell script to run an sql script via dbaccess and
mail
the results out to a few people.
When run from the command line, the output comes out with columns
displayed
horizontally.
it looks something like this:
column1 column2 column3 etc.....
data data data..........
But when run from cron (or at), it displays like this:
column1 data
column2 data
column3 data
How can I convince it that my display is wide enough to handle
displaying
the data horizontally?
Even though I'm setting my environment the same way I would when I log
in,
it seems to recognize that it's not a tty
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
I
actually tried that, but it didn't seem to have any effect.
_____
From: Jonathan Leffler [mailto:jleffler@us.ibm.com]
Sent: Tuesday, April 26, 2005 9:34 PM
To: Danny Wright
Cc: forum.subscriber@iiug.org; ids@iiug.org
Subject: RE: dbaccess output display width from cron [4822]
Sorry about the top-posting.
Set COLUMNS=132 in the environment; cron-run programs don't have a
controlling terminal, and in the absence of information to the contrary,
assume the terminal is 80 characters wide.
Use SQLCMD - one of the reasons it exists is because ISQL (and DB-Access)
are inconsistent in their output formatting.
When you sum integers, the type of the result is a (big) DECIMAL because if
you add enough big enough integers together (or count enough rows), the
result overflows your 32-bit integer value. UNLOAD puts the ####.0 there to
emphasize that the type is, in fact, a DECIMAL(32) - floating point,
32-digit decimal value. SQLCMD does the same - probably for consistency
with DB-Access, or because it uses the same formatting code as DB-Access.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
forum.subscriber@iiug.org wrote on 04/26/2005 01:37:17 PM:
> Thanks for all the input from everyone.
>
> I had already stripped out as much unnecessary stuff that I could think of
> and so I went with Jean's 2nd suggestion - unloading to a file and
> maniupulating that.
>
> By sheer luck, I happened to notice a utility I had downloaded months ago
> and forgotten about called beautify-unl.sh (I'm fairly certain it's from
the
> IIUG software depository).
>
> beautify-unl.sh lines the columns up, but leaves the delimiter and doing
an
> unload added a decimal place to the SUM and COUNT aggregate functions in
my
> sql statement, so after unloading, I run this....
>
> beautify-unl.sh <unload-file> | tr '|' '\\\\t' | sed "s/\\\\.0//g"
>
>
>
> I'm a bit curious as to why doing an unload causes 1 decimal place to be
> displayed, especially since I'm summing integers, and count should never
> return anything but an integer, but that's trivial.
>
>
>
>
> -----Original Message-----
> From: ERIEZNIKR@REPSOLYPF.COM [mailto:ERIEZNIKR@REPSOLYPF.COM]
> Sent: Tuesday, April 26, 2005 1:03 PM
> To: dwright@sherwoodfoods.com; ids@iiug.org
> Subject: RE: dbaccess output display width from cron [4812]
>
> Agree with Jean
>
> Consider Perl report features.
> Unload to file (unload to foo.bar select ....) then feed your perl report> script. If you need some example on this, just ask!
>
> Or remove innecesary fields :-)
>
> All the best.
>
> -----Mensaje original-----
> De: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] En nombre
> de Danny Wright Enviado el: Martes, 26 de Abril de 2005 01:06 p.m.
> Para: ids@iiug.org
> Asunto: dbaccess output display width from cron [4812]
>
> I set up a little shell script to run an sql script via dbaccess and mail
> the results out to a few people.
>
> When run from the command line, the output comes out with columns
displayed
> horizontally.
>
> it looks something like this:
>
> column1 column2 column3 etc.....
>
> data data data..........
>
> But when run from cron (or at), it displays like this:
>
> column1 data
> column2 data
> column3 data
>
>
>
> How can I convince it that my display is wide enough to handle displaying
> the data horizontally?
>
> Even though I'm setting my environment the same way I would when I log in,
> it seems to recognize that it's not a tty
forum.subscriber@iiug.org wrote on 04/27/2005 03:25:06 PM:
> I actually tried that, but it didn't seem to have any effect.
Oh - bother. I suspect that means that DB-Access is using an even more
archaic version of the curses library than ISQL, which does recognize
COLUMNS. Did you mention setting COLUMNS? Oh well...
Sorry - the source of the trouble (no controlling terminal for cron jobs)
is probably the correct diagnosis. I didn't attempt to check the use of
COLUMNS.
Recommend using one of the many alternatives suggested by others - or
SQLCMD suggested by me.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
> From: Jonathan Leffler [mailto:jleffler@us.ibm.com]
> Sent: Tuesday, April 26, 2005 9:34 PM
>
> Sorry about the top-posting.
>
> Set COLUMNS=132 in the environment; cron-run programs don't have a
> controlling terminal, and in the absence of information to the contrary,
> assume the terminal is 80 characters wide.
>
> Use SQLCMD - one of the reasons it exists is because ISQL (and
DB-Access)
> are inconsistent in their output formatting.
>
> When you sum integers, the type of the result is a (big) DECIMAL because
if
> you add enough big enough integers together (or count enough rows), the
> result overflows your 32-bit integer value. UNLOAD puts the ####.0
there to
> emphasize that the type is, in fact, a DECIMAL(32) - floating point,
> 32-digit decimal value. SQLCMD does the same - probably for consistency
> with DB-Access, or because it uses the same formatting code as
DB-Access.
>
> forum.subscriber@iiug.org wrote on 04/26/2005 01:37:17 PM:
> > Thanks for all the input from everyone.
> >
> > I had already stripped out as much unnecessary stuff that I could
think of
> > and so I went with Jean's 2nd suggestion - unloading to a file and
> > maniupulating that.
> >
> > By sheer luck, I happened to notice a utility I had downloaded months
ago
> > and forgotten about called beautify-unl.sh (I'm fairly certain it's
from the
> > IIUG software depository).
> >
> > beautify-unl.sh lines the columns up, but leaves the delimiter and
doing an
> > unload added a decimal place to the SUM and COUNT aggregate functions
in my
> > sql statement, so after unloading, I run this....
> >
> > beautify-unl.sh <unload-file> | tr '|' '\\\\t' | sed "s/\\\\.0//g"
> >
> > I'm a bit curious as to why doing an unload causes 1 decimal place to
be
> > displayed, especially since I'm summing integers, and count should
never
> > return anything but an integer, but that's trivial.
> >
> > -----Original Message-----
> > From: ERIEZNIKR@REPSOLYPF.COM [mailto:ERIEZNIKR@REPSOLYPF.COM]
> > Sent: Tuesday, April 26, 2005 1:03 PM
> > To: dwright@sherwoodfoods.com; ids@iiug.org
> > Subject: RE: dbaccess output display width from cron [4812]
> >
> > Agree with Jean
> >
> > Consider Perl report features.
> > Unload to file (unload to foo.bar select ....) then feed your perlreport
> > script. If you need some example on this, just ask!
> >
> > Or remove innecesary fields :-)
> >
> > All the best.
> >
> > -----Mensaje original-----
> > De: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] En
nombre
> > de Danny Wright Enviado el: Martes, 26 de Abril de 2005 01:06 p.m.
> > Para: ids@iiug.org
> > Asunto: dbaccess output display width from cron [4812]
> >
> > I set up a little shell script to run an sql script via dbaccess and
mail
> > the results out to a few people.
> >
> > When run from the command line, the output comes out with columns
displayed
> > horizontally.
> >
> > it looks something like this:
> >
> > column1 column2 column3 etc.....
> >
> > data data data..........
> >
> > But when run from cron (or at), it displays like this:
> >
> > column1 data
> > column2 data
> > column3 data
> >
> > How can I convince it that my display is wide enough to handle
displaying
> > the data horizontally?
> >
> > Even though I'm setting my environment the same way I would when I log
in,
> > it seems to recognize that it's not a tty