Views

Former Member · ‎2007 Jul 02

How can we create Views?

Former Member · ‎2007 Jul 02

Views

Why do you need views?

Creating a view by join, projection and selection

Join conditions and foreign keys

Selection of data with views

Database views

Maintenance views

Inner and outer joins

Why do you Need Views?

F1 F2 F3

F4 F5

F6 F7 F8

Table 3

Table 2

Table 1

View on the tables

View on data

that is

distributed on

more than one

table

F1 F2 F3 F5 F8

Data for an application object is often distributed on several database tables. Database

systems therefore provide you with a way of defining application-specific views on data

in several tables. These are called views.

Data from several tables can be combined in a meaningful way using a view (join). You

can also hide information that is of no interest to you (projection) or only display those

data records that satisfy certain conditions (selection).

??The data of a view can be displayed exactly like the data of a table in the extended

table maintenance.

Structure of a View – Starting Situation

Table

TABA

Table

TABB

1 1

2 1

2

Text 1 Text 3

Text 4

Text 5

Text 2

Text 6

A

B

Field 1 Field 2 Field 3 Field 4 Field 5

1 Text 1

2 Text 2

1

2

Text 3

Text 4

Text 5

Text 6

A

B

1

2

Text 3

Text 4

Text 5

Text 6

A

B

Cross-product of

tables TABA and

TABB

The structure of a view and selection of the data using this view will be shown with an

example.

Given two tables TABA and TABB. Table TABA contains 2 entries and table TABB 4

entries.

The tables are first appended to one another. This results in the cross-product of the

two tables, in which each record of TABA is combined with each record of TABB.

Structure of a View – Join Condition

Join condition: TABA - Field 1 = TABB - Field 3

Field 1 Field 2 Field 3 Field 4 Field 5

1 Text 1

2 Text 2

1

2

Text 3

Text 4

Text 5

Text 6

A

B

1

2

Text 3

Text 4

Text 5

Text 6

A

B

Reduction of

the crossproduct

??Usually the entire cross-product is not a meaningful selection. You should therefore limit

the cross-product with a join condition. The join condition describes how the records of

the two tables are related.

??In our example, Field 3 of TABB identifies Field 1 of TABA. The join condition is then:

TABA - Field 1 = TABB - Field 3

??With this join condition, all the records whose entry in Field 1 is not identical to the

entry in Field 3 are removed from the cross product. The column for Field 3 in the view

is therefore unnecessary.

Cahead Technologies

Cahead Technologies, #403/404, 2nd Floor, Chord Road Plaza, 20th Main, 1st Block, Rajajinagar, Bangalore-

560010.Tel: +91 80 3131624 3686 5220. Cell: 98860-10740. Visit us: www.cahead.com

48

Structure of a View – Field selection (Projection)

Field 1 Field 2 Field 4 Field 5

1 Text 1

2 Text 2

Text 3

Text 4

A

B

Text 5

Text 6

A

B

Field 1 Field 2 Field 5

1 Text 1

2 Text 2

Text 3

Text 4

Text 5

Text 6

Projection

??Often some of the fields of the tables involved in a view are of no interest. You can

explicitly define the set of fields to be included in the view (projection).

Structure of a View – Selection Condition

Selection condition: TABB - Field 4 = ‘A’.

Field 1 Field 2 Field 5

1 Text 1

2 Text 2

Text 3

Text 4

Text 5

Text 6

Field 1 Field 2 Field 5

1 Text 1

2 Text 2

Text 3

Text 4

Text 5

Text 6

Field 4

A

B

A

B

The set of records that can be displayed with the view can be further restricted with a

selection condition.

In our example, only those records with value 'A' in Field 4 should be displayed with the

view.

A selection condition therefore can also be formulated with a field that is not contained

in the view.

How are Tables Linked to Views?

MANDT CARRID CONNID FLDATE BOOKID CUSTOMID ... SBOOK

MANDT CARRID CONNID ... CITYFROM ... CITYTO ... SPFLI

001

MANDT ID NAME CITY ... SCUSTOM

001 122356 Smith New York ...

122356

...

001

New York

Berlin Tokyo

AA Berlin

AA

LH

48

324

48

...

3689

3690

...

Example: Travel agencies sometimes have to check which customer is booked on

which flights. The corresponding data is distributed on several tables:

SCUSTOM: Customer data, such as the customer number, name and address

SBOOK: Booking data, such as the carrier, flight number and passenger (customer

number)

SPFLI: Flight data, such as the city of departure and city of arrival

You have to create a view on tables SCUSTOM, SBOOK and SPFLI to obtain the booking

data.

In this case the join conditions are:

SBOOK-MANDT = SCUSTOM-MANDT

SBOOK-CUSTOMID = SCUSTOM-ID

SPFLI-MANDT = SBOOK-MANDT

SPFLI-CARRID = SBOOK-CARRID

SPFLI-CONNID = SBOOK-CONNID

Structure of the View

MANDT ID NAME CITY CARRID CONNID FLDATE BOOKID CITYFROM CITYTO

001

122356 Smith New York AA 48 3689 New York Berlin

122356 Smith New York LH 324 3690 Berlin Tokyo

View SCUS_BOOK for customer bookings

4.9.1999

9.9.1999

You can get the bookings for a particular customer by selecting the corresponding

records for keys MANDT and CUSTOMID in table SBOOK.

You can get the flight data from table SPFLI for each booking in table SBOOK by

selecting the corresponding record for the keys MANDT, CARRID and CONNID from

table SPFLI.

You can display only the customer bookings which were not canceled using the view

with the following selection condition:

SBOOK-CANCELED <> 'X'

The join conditions can also be derived from the existing foreign key relationships.

Copying the join conditions from the existing foreign keys is supported in the

maintenance transaction.

The field names of the underlying table fields are normally used as field names in the

view. However, you can also choose a different field name. This is necessary for

instance if two fields with the same name are to be copied to the view from different

tables. In this case you must choose a different name for one of the two fields in the

view.

Data Selection with Views

REPORT CUSBOOK1.

PARAMETERS: CUSTOMID LIKE SBOOK-CUSTOMID.

DATA: BOOKINGS TYPE SCUS_BOOK.

WRITE: / ‘Existing bookings for customer’, CUSTOMID, ‘:’.

SELECT * FROM SCUS_BOOK INTO BOOKINGS

WHERE CUSTOMID = CUSTOMID.

WRITE: / ‘CUSTOMER’, BOOKINGS-NAME, ‘booked for’,

BOOKINGS-CARRID,BOOKINGS-CONNID, ‘from’,BOOKINGS-CITYFROM,

‘to’,BOOKINGS-CITYTO, ‘on’,BOOKINGS-FLDATE.

ENDSELECT.

IF SY-SUBRC <> 0.

WRITE: / ‘No bookings exist’.

ENDIF.

??You would get the same results using nested SELECT statements:

SELECT * FROM SCUSTOM WHERE ID = CUSTOMID.

SELECT * FROM SBOOK WHERE CUSTOMID = SCUSTOM-ID.

SELECT * FROM SPFLI WHERE CARRID = SBOOK-CARRID AND

CONNID = SBOOK-CONNID

WRITE: / ‘Customer’, SCUSTOM-NAME, ‘booked on’, SPFLI-CARRID, SPFLICONNID,

‘from’, SPFLI-CITYFROM, ‘to’, SPFLI-CITYTO, ‘on’, SBOOK-FLDATE.

ENDSELECT.

??Selection with a database view, however, is usually more efficient than selection with a

nested SELECT statement.

As of Release 4.0 you can formulate the join condition directly in OPEN SQL.

A view has type character and can be accessed in programs like all other types and can

be used to define data objects.

Database Views

F6 F7 F8

Table 3

F4 F5

Table 2

F1 F2 F3

Table 1

View definition in the ABAP Dictionary

View definition in

the database

Is created in the DB

during activation

ABAP program

F1 F2 F3 F5 F8

Database interface

A database view is defined in the ABAP Dictionary and automatically created on the

database during activation. Accesses to a database view are passed directly to the

database from the database interface. The database software performs the data

selection.

If the definition of a database view is changed in the ABAP Dictionary, the view created

on the database must be adjusted to this change. Since a view does not contain any

data, this adjustment is made by deleting the old view definition and creating the view

again in the ABAP Dictionary with its new definition.

The maintenance status defines whether you can only read with the view or whether

you can also write with it. If a database view was defined with more than one table, this

view must be read only.

The data read with a database view can be buffered. View data is buffered analogously

to tables. The technical settings of a database view control whether the view data may

be buffered and how this should be done. The same settings (buffering types) can be

used here as for table buffering. The buffered view data is invalidated when the data in

one of the base tables of the view changes.

Includes in Database Views

F 7 F 8

TABB

iinclluded iin

viiew

Database view on TABA, TABB and TABC

TABA TABB TABC

F 1 F 3 F 4 F 5 F 6 F 8

F 1 F 2 F 3 F 4 F 5 F 6

You can include entire tables in database views. In this case all the fields of the included

table become fields of the view (whereby you can explicitly exclude certain fields). If

new fields are included in the table or existing fields are deleted, the view is

automatically adjusted to this change. A new or deleted field is therefore automatically

included in the view or deleted from it.

If an append structure is added to a table included in a view, the fields added with the

append structure are automatically included in the view.

To include a table in a view, you must enter the character '*' in field View field in the

view maintenance, the name of the table to be included in the field Table and the

character '*' again in the field Field name.

??If you do not want to include a field of the included table in the view, proceed as

follows:

Enter a '-' in the field View field.

Enter the name of the included table in the field Table.

Enter the name of the field in the field Field name.

Maintenance Views

F6 F7 F8

Table 3

F1 F2 F3

Table 1

Table 2

F4 F5

Maintenance

view on the

tables Data exchange with

the maintenance view

Foreign key Foreign key

F1 F2 F3 F5 F8

Application object

Data that is distributed on more than one table often forms a logical unit, called an

application object. You should be able to display, change and create the data of such an

application object together. Users usually are not interested in the technical

implementation of the application object, such as the distribution of the data on several

tables.

You can maintain complex application objects in a simple way using a maintenance

view. The data is automatically distributed on the underlying database tables.

All the tables used in a maintenance view must be linked with a foreign key. This means

that the join conditions are always derived from the foreign key in the maintenance

view. You cannot enter the join conditions directly as in a database view.

A maintenance interface with which the data of the view can be displayed, changed and

created must be generated from the definition of a maintenance view in the ABAP

Dictionary.

When the maintenance interface is created, function modules that distribute the data

maintained with the view on the underlying tables are automatically generated.

The maintenance interface is generated with the Transaction Generate Table View

(Transaction SE54) or from the view maintenance screen with Environment ->

Tab.maint.generator.

Inner and Outer Joins

What is displayed with the view?

Inner join Outer join

Table TABA Table TABB

Join condition

Field 1 Field 2 Field 4

A Text 1

B Text 2

Text 3

Text 4

Field 1 Field 2 Field 4

A Text 1

B Text 2

Text 3

Text 4

C Text 5

A

B

Text 1

Text 2

Field 1 Field 2

C Text 5

A

B

Text 3

Text 4

Field 3 Field 4

The set of data that can be selected with a view greatly depends on whether the view

implements an inner join or an outer join.

With an inner join, you only get those records which have an entry in all the tables

included in the view. With an outer join, on the other hand, those records that do not

have a corresponding entry in some of the tables included in the view are also selected.

The hit list found with an inner join can therefore be a subset of the hit list found with

an outer join.

Database views imp lement an inner join. You only get those records which have an

entry in all the tables included in the view.

Maintenance views implement an outer join.

Former Member · ‎2007 Jul 02

Go to SE11,

select the radio button for view. enter a name eg. ztable

click create.

select the tables which you want...

and the fields.

and create...

please revert back if you have any questions.

Regards

Dinesh

ps: reward points

former_member150733 · ‎2007 Jul 02

Custom Views can be created by going to T.Code SE11.

More details :

http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ecab446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/en/66/33f52010dd11d6999300508b5d5211/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ecd2446011d189700000e8322d00/frameset.htm

Former Member · ‎2007 Jul 02

Hi ,

go to se11.

enter a name for the view and click create.

Radio button option will be displayed

asking you to select one among them

1.Database view

2.Projection view

3.Maintenance view

4.Help View

click on the required type of view.

Hope it helps you.

Former Member · ‎2007 Jul 03

What is the Different Types and Usage of Views
This is often asked in an interview about the types of views

:

The followings are different types of views:

- Database View (SE11)

Database views are implement an inner join, that is, only records of the primary table (selected via the join operation) for which the corresponding records of the secondary tables also exist are fetched. Inconsistencies between primary and secondary table could, therefore, lead to a reduced selection set.

In database views, the join conditions can be formulated using equality relationships between any base fields. In the other types of view, they must be taken from existing foreign keys. That is, tables can only be collected in a maintenance or help view if they are linked to one another via foreign keys.

- Help View ( SE54)

Help views are used to output additional information when the online help system is called.

When the F4 button is pressed for a screen field, a check is first made on whether a matchcode is defined for this field. If this is not the case, the help view is displayed in which the check table of the field is the primary table. Thus, for each table no more than one help view can be created, that is, a table can only be primary table in at most one help view.

- Projection View

Projection views are used to suppress or mask certain fields in a table (projection), thus minimizing the number of interfaces. This means that only the data that is actually required is exchanged when the database is accessed.

A projection view can draw upon only one table. Selection conditions cannot be specified for projection views.

- Maintenance View ( SE54 )

Maintenance views enable a business-oriented approach to looking at data, while at the same time, making it possible to maintain the data involved. Data from several tables can be summarized in a maintenance view and maintained collectively via this view. That is, the data is entered via the view and then distributed to the underlying tables by the system.

1.Creating Help Views

Procedure

Enter an explanatory short text in the field Short text.
You can for example find the view at a later time using this short text.

Enter the primary table of the view under Tables in the Tables/Join conditions tab page.
Only tables that are linked with the primary table (indirectly) with a foreign key can be included in the view.

Save your entries.
You are asked to assign the help view a development class. You can change this development class later with Extras ® Object directory entry.

If required, include more tables in the view. In a help view you can only include tables that are linked to one another with foreign keys.
Position the cursor on the primary table and choose Relationships. All existing foreign key relationships of the primary table are displayed. Select the foreign keys and choose Copy. The secondary table involved in such a foreign key is included in the view. The join conditions derived from the foreign keys (see Foreign Key Relationship and Join Condition) are displayed.

You can also include tables that are linked with a foreign key to one of the secondary tables already included. To do this, place the cursor on the secondary table and choose Relationships. Then proceed as described above.

For maintenance and help views, there are certain restrictions on the foreign keys with which the tables can be included in the view (see Restrictions for Maintenance and Help Views). The foreign keys violating these conditions are displayed at the end of the list under the header Relationships with unsuitable cardinality.

On the View fields tab page, select the fields that you want to copy to the view. The key fields of the primary table were automatically copied to the view as proposals.
Choose Table fields. All the tables contained in the view are listed in a dialog box. Select a table. The fields of the table are now displayed in a dialog box. Select the required fields in the first column and choose Copy.

On the Selection conditions tab page, you can (optionally) formulate restrictions for the data records to be displayed with the view (see Maintaining Selection Conditions for Views).
The selection conditions define the data records that can be selected with the view.

Choose .
Result

The view is now activated. At activation, a log is written; it can be displayed with Utilities ® Activation log. If errors or warnings occurring when the view was activated, they are displayed directly in the activation log.

2.Creating a Database View

Procedure

Enter an explanatory short text in the field Short text.
You can for example find the view at a later time using this short text.

Define the tables to be included in the view in the Tables field of the Tables/Join conditions tab page.
Keep in mind that you can only include transparent tables in a database view.

Link the tables with join conditions.
If there are suitable foreign keys between the tables, you should copy the join conditions from these foreign keys (see Foreign Key Relationships and Join Conditions).

Place the cursor on a table name and choose Relationships. All foreign keys to other tables defined for this table are displayed. Select the foreign keys and choose Copy. The join condition is now derived from the definitions in the foreign key.

If you only want to see the foreign key relationship existing between two tables, you must first select these two tables (click on the first column of the input area Tables) and then choose Relationships.

On the View fields tab page, select the fields that you want to copy to the view.
Choose Table fields. All the tables contained in the view are displayed in a dialog box. Select a table. All the fields contained in this table are displayed. You can copy fields by selecting them in the first column and choosing Copy.

You can also include an entire table in the view (see Includes in Database Views).

On the Selection conditions tab page, you can (optionally) formulate restrictions for the data records to be displayed with the view (see Maintaining Selection Conditions for Views).
The selection conditions define the data records that can be selected with the view.

With Goto ® Technical settings, you can (optionally) maintain the technical settings of the database view.
You can define whether and how the database view should be buffered here. Proceed as for the technical settings of a table (see Maintaining Technical Settings). Note that only the settings for buffering can be maintained for database views.

On the Maintenance status tab page, select the maintenance status of the database view.
If the view contains more than one table, the maintenance status read only cannot be altered.

Save your entries. You are asked to assign the view a development class.
You can change this development class later with Goto ® Object directory entry.

Choose .
Result

When a database view is activated, the corresponding view is also automatically created in the database if the base tables of the view were already created there.

At activation, a log is written; it can be displayed with Utilities ® Activation log. If errors or warnings occurring when the view was activated, they are displayed directly in the activation log.

If the base tables are not yet created in the database, this is recorded in the activation log. The view is nevertheless activated in the ABAP Dictionary. In this case you can create the relevant view on the database later with the database utility.

3.Creating Maintenance Views

Procedure

Enter an explanatory short text in the field Short text.
You can for example find the view at a later time using this short text.

Enter the primary table of the view under Tables in the Tables/Join conditions tab page.
Only those tables that are linked with the primary table (indirectly) with a foreign key can be included in the maintenance view.

If required, include more tables in the view. In a maintenance view you can only insert tables that are linked to one another with foreign keys.
Place the cursor on the primary table and choose Relationships. All existing foreign key relationships of the primary table are displayed. Select the required foreign key and choose Copy. The secondary table used in such a foreign key is included in the view. The join conditions derived from the foreign keys (see Foreign Key Relationship and Join Condition) are displayed.

You can also insert tables that are linked by foreign key with one of the secondary tables that was already inserted. To do this, place the cursor on the secondary table and choose Relationships. Then proceed as described above.

For maintenance and help views, there are certain restrictions on the foreign keys with which the tables can be included in the view (see Restrictions for Maintenance and Help Views). The foreign keys violating these conditions are displayed at the end of the list under the header Relationships with unsuitable cardinality.

On the View fields tab page, select the fields that you want to copy to the view.
Choose Table fields. All the tables contained in the view are displayed in a dialog box. Select a table. The fields of the table are now displayed in a dialog box. You can copy fields by selecting them in the first column and choosing Copy.

All key fields of the primary table must be included in a maintenance view. In addition, all key fields of secondary tables that are not involved in the foreign key (that is, which are not linked via a join condition to a key field already included in the view) must be included in the view.

This ensures that the records inserted with a maintenance view can be written correctly in the tables contained in the view.

On the Selection conditions tab page, you can (optionally) formulate restrictions for the data records that can be displayed with the view (see Maintaining Selection Conditions for Views).
The selection conditions define the data records that can be selected with the view.

In the Maintenance status tab page, define the maintenance status of the view.
The maintenance status defines how you can access the view data with the standard maintenance transaction (SM30).

Choose .
At activation, a log is written; it can be displayed with Utilities ® Activation log. If errors or warnings occurring when the view was activated, the activation log is automatically displayed.

Go to Transaction SE54 with Environment ® Tab.maint.generator.
From the view definition you can generate maintenance modules and maintenance interfaces that distribute the data entered with the view to the base tables of the view. You can find more information in Creating a Maintenance Dialog.

4 .Creating Projection Views

Procedure

Enter an explanatory short text in the field Short text.

You can for example find the view at a later time using this short text.

Enter a table name in the field Base table.

A projection view always contains exactly one table.

Select the fields of the base table that you want to include in the view.

Choose Table fields. The fields of the table are now displayed in a dialog box. You can copy fields by selecting them in the first column and choosing Copy.

Save your entries.

You are asked to assign the view a development class. You can change this development class later with Goto ® Object directory entry.

Choose .

Result

The help view is activated. At activation, a log is written; it can be displayed with Utilities ® Activation log. If errors or warnings occurring when the view was activated, they are displayed directly in the activation log.

reward points if it is usefulll .....

Girish

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Views