Re: Whitespace question

From: Imprecise <f_puhan_at_precise.com>
Date: Mon, 10 Jun 2002 14:47:51 -0400
Message-ID: <f_puhan-E267E5.14475110062002@vienna7.his.com>

In article <H25N8.3029$H67.16152_at_tor-nn1.netcom.ca>,  "Syltrem" <syltremspammenot_at_videotron.com> wrote:

> Hello!
>
> > SELECT 'xxx' a, ' ' b
> > FROM dual
> > WHERE b IS NOT NULL
> >
> > but that seems so obvious that I wonder if I have misunderstood your
> question.
> >
>
> a) I still would like a to print
> b) since b is output on its own line (remember, linesize is 80 and both
> columns are 80) then I would like sqlplus to skip writing this line when b
> is empty (null or all spaces).
> c) I'm looking for this too! (I think I'm gonna have to write my own
> function): a function that converts multiple spaces or tabs (in a string) to
> a single space
> .
> Thanks for trying!
> If this is still not clear, maybe my English skills are to blame :-)
>
> --
>
> Syltrem
> http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> To reply to myself directly, remove .spammenot from my address
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> a écrit dans le message de news:
> 3D04CCDE.B9CA5BB2_at_exesolutions.com...
> > Syltrem wrote:
> >
> > > Good morning everyone!
> > >
> > > I've been researching for :
> > > a) a function that converts multiple spaces or tabs to a single space in
> a
> > > string
> > > b) a way to suppress blank lines from the output of a SELECT in SQL*Plus
> > >
> > > For b) here's an example
> > > set linesize 80
> > > column a format a80
> > > column b format a80
> > > select 'xxx' a, ' ' b from dual;
> > >
> > > Since b is empty, a blank line is displayed. I would like to know if
> there's
> > > any way to avoid printing blank lines. The thing is (in real life) b is
> > > almost always empty so the reports looks like double-spaced.
> > >
> > > Merci beaucoup!

I faced a similar situation and wound up using a DECODE structure much like this:

SELECT

   '===========================',

   DECODE(a.col1,NULL,NULL,CHR(10))||a.col1||    DECODE(a.col2,NULL,NULL,CHR(10))||a.col2 FROM
   mytab a
WHERE
   ...
/

Since I don't know the nature of your data, this example is a bit imprecise, but essentially it tests for NULL values in each column, and simply prints a newline character if there is no data in the column.

-- 
The underscore character does not belong in my address. You know the drill...
***
Anyone sufficiently smart enough to configure and use USEnet for research should
be smart enough to Read The Freakin' Documentation!

Received on Mon Jun 10 2002 - 13:47:51 CDT