Re: How to specify column header titles in a SQL CREATE TABLE statement?

"Philip Sherman" <psherman_at_ameritech.net> wrote in message news:Tk9bd.14602$Bw.1873_at_newssvr31.news.prodigy.com...
> Rhino wrote:
>
> > If you want embedded blanks within the column heading, you need to embed
the
> > column heading within quotes. For example:
> >
> > select lastname as "Employee Last Name", salary as "Employee Pay"
> > from employee
> > where empno in ('000010', '000020');
> >
> > results in
> >
> > EMPLOYEE LAST NAME EMPLOYEE PAY
> > -------------------------- -----------------
> > HAAS 52750.00
> > THOMPSON 41250.00
> >
> > [Please note that I "took some liberties" with the result sets shown in
> > these examples due to the proportional fonts used in my newsreader. In
the
> > real world:
> > - the lastname values will be left-justified under the LASTNAME heading
> > - the salary values will be right-justified under the SALARY heading.
> > - the headings will always be uppercase if the query is executed in the
DB2
> > command line but will match the case of the query if the query is
executed
> > in the Command Center]
>
> Headings specified in the "as" clause will be used EXACTLY AS RECEIVED
> by the DB2 command processor. If you are getting folding to upper case
> then you are most likely not correctly specifying the column headings.
>
> Under RHEL3, UDB 8.1 FP7:
>
> db2 "select lastname as ""Lastname"" from employee where lastname like
'L%'"
>
> LASTNAME
> ---------------
> LUCCHESSI
> LUTZ
> LEE
>
> appears correct and returns the data but does not have the correct
> column heading! The operating system command line processor gets this
> long before DB2 does and alters it.
>
> db2 "select lastname as \"Lastname\" from employee where lastname like
'L%'"
>
> Lastname
> ---------------
> LUCCHESSI
> LUTZ
> LEE
>
> yields exactly the results that are expected.
>
>
I stand corrected; my column headings were coming out upper case on the command line because I had not quoted them correctly.

However, I don't get quite the same results that you did from your two queries. I am running DB2 V7.2.7 on Windows XP.

Given:
db2 "select lastname as ""Lastname"" from employee where lastname like 'L%'"

I get the following when I execute it on the DB2 command line: SQL0100N The string constant beginning with ""Lastname from employee where lastname like 'L%'" does not have an ending string delimiter. SQLSTATE=42603

I can eliminate this error by putting *three* consecutive quotes after the alias, i.e.
db2 "select lastname as ""Lastname""" from employee where lastname like 'L%'"

This results in:
Lastname

In any case, the original poster probably knows more now about column headings that he really wanted to know ;-)

Rhino Received on Wed Oct 13 2004 - 22:08:01 CEST