Re: Whitespace question

Bricklen Anderson wrote:

> Is it permissible to rewrite your query slightly like:
> select distinct a,b
> from temp
> where (a is not null and b is not null);
>
> Syltrem wrote:
> >
> > I hate to say that, but I'm still getting a blank line wherever B is null.
> >
> > A
> > ----------------------------------------------------------------------------
> > ----
> > A1
> >
> > A
> > ----------------------------------------------------------------------------
> > ----
> > A2
> >
> > A
> > ----------------------------------------------------------------------------
> > ----
> > A3
> >
> > A
> > ----------------------------------------------------------------------------
> > ----
> > B
> > ----------------------------------------------------------------------------
> > ----
> > A4
> > B1
> >
> > And I should add, to be more specific about my request, that I would like
> > SQLPLUS to skip printing blank lines. I don't want to do it
> > programmatically. I can do this from Powerhouse (SET NOBLANKS), and other
> > languages too.
> > The real story is: I have BREAK BY statements, and that makes sqlplus not
> > print the value if it is the same as the one before (as you well know). So a
> > little change to your table gives:
> >
> > UPDATE TEMP SET A = 'A';
> > BREAK BY A
> > SELECT a,b from temp;
> > A
> >
> > B1
> > 4 rows selected.
> >
> > --> lines 2 and 3 are blank. That they do not print is what I want..
> >
> > Is this possible?
> >
> > Thanks
> > --
> >
> > Syltrem
> > http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> > To reply to myself directly, remove .spammenot from my address
> >
> > "Richard Spee" <rhpspee_at_wxs.nl> a écrit dans le message de news:
> > ae2s48$icd$1_at_reader12.wxs.nl...
> > Run the following script in Sqlplus
> >
> > /*
> > I'm not very proud of it, but the result is what you want
> > */
> >
> > create table temp (a varchar2(10)
> > ,b varchar2(10)
> > );
> > insert into temp values ('A1',null);
> > insert into temp values ('A2',null);
> > insert into temp values ('A3',null);
> > insert into temp values ('A4','B1');
> >
> > set linesize 80
> > set pagesize 0
> > select rpad('A',80,' ')
> > || rpad('-',80,'-')
> > || decode(b,null,null,rpad('B',80,' '))
> > || decode(b,null,null,rpad('-',80,'-'))
> > || rpad(a,80,' ')
> > || decode(b,null,null,rpad(b,80,' '))
> > from temp
> > /
> >
> > "Syltrem" <syltremspammenot_at_videotron.com> wrote in message
> > news:1W2N8.3017$H67.15842_at_tor-nn1.netcom.ca...
> > > 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!
> > >
> > > --
> > >
> > > Syltrem
> > > http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
> > > To reply to myself directly, remove .spammenot from my address
> > >
> > >
> > >

There may be a translation issue here and his English is far better than my French. So here's another shot at it.

1. Look at the NVL function in your SQL
2. Look at setting trimout to off in SQL*Plus
3. Look at setting head to off in SQL*Plus

Daniel Morgan Received on Mon Jun 10 2002 - 15:08:18 CDT