Re: sql*Plus formatting question

On Jun 5, 1:57 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 5, 11:43 am, EdStevens <quetico_..._at_yahoo.com> wrote:
>
>
>
> > On Jun 5, 10:31 am, Valentin Minzatu <valentinminz..._at_yahoo.com>
> > wrote:
>
> > > On Jun 5, 11:27 am, EdStevens <quetico_..._at_yahoo.com> wrote:
>
> > > > Platform: Oracle EE 10.2 on HPUX
>
> > > > Given this snippet of code:
>
> > > > set pages 999
> > > > set lines 512
> > > > set trimspool on
> > > > --
> > > > spo plus.log
> > > > --
> > > > select name "Name",
> > > > owe||to_number(grade) "Grade",
> > > > from ...
>
> > > > where owe is varchar2(1)
> > > > grade is varchar2(3)
>
> > > > and grade is actually a 2-digit number, all of which are in the range
> > > > '01' to '09'
>
> > > > When I run the query, the length of the 'Grade' column comes out to 41
> > > > characters, left justified. I've tried bracketing the results ('['||
> > > > owe||to_number(grade)||']') to see if I'm getting a bunch of leading
> > > > or trailing spaces, but that has shown that the data returned is
> > > > exactly the expected two characters. Have tried enclosing the
> > > > to_number inside a to_char (since I'm concatenating the results to a
> > > > varchar), but to no effect. If I don't concatenate owe and grade
> > > > (selecting them as two separate columns) there is no problem.
>
> > > Have you tried: col "Grade" format a3?
>
> > Which, of course, works -- but begs the question of why it is needed
> > in this instance. ;-0- Hide quoted text -
>
> > - Show quoted text -
>
> You cannot, of course, know both the question AND the answer lest the
> universe morph into something unknown and uncontrollable ... :)
>
> David Fitzjarrell

I think it has already. About the time I think I've got a grip on a small piece of knowledge, something comes along that doesn't fit. Much like Fyneman's description of how scientific theory is constantly refined due to observation of new phenomenon.

So I experimented a bit more.

I found that if I

SELECT owe || grade "Grade"
FROM .... (with no COLUMN format defintions)

the resulting "Grade" column is the expected 4 characters .. the sum of the length of the two varchar2 columns. However, if I

SELECT owe || to_number(grade) "Grade"
FROM ... The resulting column (header) is 41 characters.

And if I

SELECT '[' || owe || to_number(grade) || ']' "Grade" FROM ... The resulting column (header) is still 41 characters wide, even though the returned data, counting the square bracket literals, is only 4 characters wide.

Am I missing the obvious, or is there some subtle effect going on here? Received on Wed Jun 06 2007 - 08:02:52 CDT