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

[Thomas Jerkins]

When I write a create table SQL statement I want to add some information about the column heading of each column.
How does the exact syntax for the create table look like (which includes this column data look)?

How do I add later column headings ?

Tom

[Rhino]

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


"Thomas Jerkins" <tomjerk@hotmai l.comwrote in message
news:ckhavu$ihj $01$1@news.t-online.com...

When I write a create table SQL statement I want to add some information

about the column heading of each column.

How does the exact syntax for the create table look like (which includes

this column data look)?

>
How do I add later column headings ?
>

I am assuming that you are using the phrase 'column heading' to mean the
title that appears above a column when you run an SQL Select from the DB2
command line or in the DB2 Command Center. For example, given:

select lastname, salary
from employee
where empno in ('000010', '000020');

results in:

LASTNAME SALARY
------------ ---------
HAAS 52750.00
THOMPSON 41250.00

I assume that your question refers to 'LASTNAME' and 'SALARY' in this result
and that you want to control what these headings are. If that's NOT what you
mean by 'column headings', please clarify.

It is possible that SQLServer, Oracle and DB2 handle these column headings
the same way as a result of complying with the same standards. Then again,
they may not not; I am only answering with respect to DB2.

The Create Table statement does not have any effect on the column heading
that appears when you query the table. To control the column heading, you
use an 'as' expression within the Select statement. For example:

select lastname as Employee_Last_N ame, salary as Employee_Pay
from employee
where empno in ('000010', '000020');

results in:

EMPLOYEE_LAST_N AME EMPLOYEE_PAY
--------------------------- ------------------
HAAS 52750.00
THOMPSON 41250.00

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]

Rhino

[Philip Sherman]

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

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.


Phil Sherman

[Rhino]

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


"Philip Sherman" <psherman@ameri tech.netwrote in message
news:Tk9bd.1460 2$Bw.1873@newss vr31.news.prodi gy.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
-----------
LUCCHESSI
LUTZ
LEE

as you predicted.

However, when I execute (on the DB2 command line):
db2 "select lastname as \"Lastname\" from employee where lastname like 'L%'"

I get:
Lastname
-----------
LUCCHESSI
LUTZ
LEE

just as you described.

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

Rhino

[Knut Stolze]

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

Rhino wrote:

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
-----------
LUCCHESSI
LUTZ
LEE
>
as you predicted.
>
However, when I execute (on the DB2 command line):
db2 "select lastname as \"Lastname\" from employee where lastname like
'L%'"
>
I get:
Lastname
-----------
LUCCHESSI
LUTZ
LEE
>
just as you described.
>
In any case, the original poster probably knows more now about column
headings that he really wanted to know ;-)

You might want to look at the documentation for the DOS command prompt (or
whatever you use) to figure out what it does with the quotes. A Unix
shell, given

"abc \" def"

will pass the following string to the application

abc " def

The DOS command prompt works on the parameters as well somehow, and you have
to counter that to get the " to DB2.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena