Entering content frame

This graphic is explained in the accompanying text View Tables Locate the document in its SAP Library structure

A view table is a view of one or more existing tables. Parts of these tables are hidden, and others remain visible.

Creating a View Table

To create a view table, use the CREATE VIEW statement.

 

CREATE VIEW customer_addr (cno, title, name, zip, city, state, address)
  AS SELECT customer.cno, customer.title, customer.name, customer.zip,
    city.name, city.state, customer.address
    FROM customer, city
    WHERE customer.zip = city.zip
The customer_addr view table consists of information from the customer and city tables.

If the tables are filled with the data specified in HOTELDB_SIMPLE, the following data can be found in the customer_addr view table:

SELECT * FROM customer_addr ORDER BY cno

Result

CNO

TITLE

NAME

ZIP

CITY

STATE

ADDRESS

3000

Mrs

Porter

10580

New York

NY

1340 N.Ash Street, #3

3100

Mr

Brown

48226

Detroit

MI

1001 34th Str., APT.3

3200

Company

Datasoft

90018

Los Angeles

CA

486 Maple Str.

3300

Mrs

Brian

75243

Dallas

TX

500 Yellowstone Drive, #2

3400

Mrs

Griffith

20005

Washington

DC

3401 Elder Lane

3500

Mr

Randolph

60615

Chicago

IL

340 MAIN STREET, #7

3600

Mrs

Smith

75243

Dallas

TX

250 Curtis Street

3700

Mr

Jackson

45211

Cincinnati

OH

133 BROADWAY APT. 1

3800

Mrs

Doe

97213

Portland

OR

2000 Humboldt Str., #6

3900

Mr

Howe

75243

Dallas

TX

111 B Parkway, #23

4000

Mr

Miller

95054

Santa Clara

CA

27 5th Str., 76

4100

Mrs

Baker

90018

Los Angeles

CA

200 MAIN STREET, #94

4200

Mr

Peters

92714

Irvine

CA

700 S. Ash Str., APT.12

4300

Company

TOOLware

20019

Washington

DC

410 Mariposa Str., #10

 

When you create a view table, you can rename and rearrange columns. You can link together several tables. To define a view table, you can use every SELECT statement that does not contain ORDER BY.

You can always use a view table name in SELECT statements and, in special cases, in INSERT, UPDATE, and DELETE statements.

See also:

CREATE VIEW Statement (create_view_statement)

 

Dropping a View Table

To drop a view table, use the DROP VIEW statement.

 

DROP VIEW customer_addr

 

You can use this SQL statement to drop the view definition; this does not affect the table contents.

See also:

DROP VIEW Statement (drop_view_statement)

 

Leaving content frame