Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Whitespace question

Re: Whitespace question

From: Bricklen Anderson <bricklen_at_shaw.ca>
Date: Mon, 10 Jun 2002 19:33:23 GMT
Message-ID: <3D04FDFF.487265A1@shaw.ca>


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
> >
> >
> >
Received on Mon Jun 10 2002 - 14:33:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US