Solved: Re: Tables & Views!

Former Member · ‎2007 Aug 02

Hi Experts,

This is Guru, new bie to ABAP. I need your help to know about TABLES & VIEWS.

It will be greatful if you provide some tutorials saying "What is table and views ? How to create Table and views ?" like that from the basic to end.

Any useful answers will be appreciated.

Thanks in Advance,

Guru.

Former Member · ‎2007 Aug 02

welcome to SDN forum.

First up all, do you have DEVELOPER access key to create a Table? have you previously created any object in the system?

Take the developer access key and create the table

Goto SE11 Tcode

enter table name

press create

enter description

enter delivery class as 'A'.

press fields

enter some 3 fields with their data elements or press predefined type

enter the data type as CHAR and length some 10 like that enter some fields and save

press technical settings button on application toolbar

enter data class and size category and save and activate

Database table

Tables can be defined independently of the database in the ABAP Dictionary. The fields of the table are defined together with their (database-independent) data types and lengths.

A table definition in the ABAP Dictionary has the following components:

Table fields: The field names and the data types of the fields contained in the table are defined here.

Foreign keys: The foreign keys define the relationships between this table and other tables.

Technical settings: The technical settings define how the table is created on the database.

Indexes: Indexes can be defined for the table to speed up data selection from the table.

There are three categories of database tables in the ABAP Dictionary.

A physical table definition is created in the database for the table definition stored in the ABAP Dictionary for transparent tables when the table is activated. The table definition is translated from the ABAP Dictionary to a definition of the particular database.

On the other hand, pooled tables and cluster tables are not created in the database. The data of these tables is stored in the corresponding table pool or table cluster. It is not necessary to create indexes and technical settings for pooled and cluster tables

In Source System, go to SE11 and create the view, then go to RSO2 to create datasource.

2) Specify the name of the datasource in the field of RSO2 and hit Create.

3) Give the Application component and description.

4) In View/Table field field specify the ZView name.

5) Then click Save.

6) It will take you to the extract structure screen.

7) If you want to hide/select any of the field you can do that here, otherwise press enter.

😎 Change the package of the datasource and specify the transport request number to it.

9) Test the extractor in RSA3.

10) Replicate in BW and map that to InfoSource.

Former Member · ‎2007 Aug 02

Hi,

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.

TABLES:

step 1. Go to transaction se11.

step 2. write a table name prefix with z.

step 3. Give description.

step 4. It comes to delivery and maintainance, In delivery class u have 3 types u can select any of those 3. I have selected as A(master and transaction data).

Along with this u have to mention ur data browser as Display/ maintainance allowed only.

step 5. Go to technical settings, specify ur data class (APPL0) and size.

step 6. Create your fields with data element and domain.

step 7. While u enter ur data element ( the name u give should prefix with z) double click on that.

step 8. Now u have two options either to create domain or data type. After creating this, it will ask for field label, mention the field label.

step 9. Now activate at the same screen and come back u'll come to data element screen then again activate. Finally u'll come to first screen.

step 10. Follow steps 6 to 9 for more fields.

step 11. then activate.

step 12. To create entries in the table. On menu bar u have utilities, in that u have table contents, in that u have create entries.

step 13. After every entry save the data. That will be saved in the databaase.

Reward if it helps..

Regards,

Omkar.

seshatalpasai_madala · ‎2007 Aug 02

HI,

http://help.sap.com/saphelp_47x200/helpdata/en/cf/21ea43446011d189700000e8322d00/content.htm

http://help.sap.com/saphelp_47x200/helpdata/en/cf/21ec5d446011d189700000e8322d00/content.htm

Regards,

Sesh

Former Member · ‎2007 Aug 02

Hi,

VIEWS

There are 4 types.

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.

Please have a look at below link. It will help you.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ed06446011d189700000e8322d00/frameset.htm

for more detailed info look on:

http://www.sap-img.com/abap/what-is-the-different-types-and-usage-of-views.htm

&

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/abap+dictionary&;

TABLES

Pool Tables

1) Many to One Relationship.

2) Table in the Dictionary has the different name, different number of fields, and the fields have the different name as in the R3 Table definition.

3) It can hold only pooled tables.

cluster Tables:

1) These are logical tables that are arranged as records of transparent tables.

2) One cannot use Native SQL on these tables (only Open SQL).

3) They are not manageable directly using database system tools.

These two table are used to store system data and onother for configural data.

if u want to fetch the data from DB table u need to go for trasperent tables only.

Bcoz transperent tables are one - one relation.

Refer to this link

http://help.sap.com/saphelp_nw04/helpdata/en/81/415d363640933fe10000009b38f839/frameset.htm

INTERNAL TABLES

There are 3 types of Internal tables.

Standard Internal Tables:

Standard tables have a linear index. You can access them using either the index or the key. If you use the key, the response time is in linear relationship to the number of table entries. The key of a standard table is always non-unique, and you may not include any specification for the uniqueness in the table definition.

This table type is particularly appropriate if you want to address individual table entries using the index. This is the quickest way to access table entries. To fill a standard table, append lines using the (APPEND) statement. You should read, modify and delete lines by referring to the index (INDEX option with the relevant ABAP command). The response time for accessing a standard table is in linear relation to the number of table entries. If you need to use key access, standard tables are appropriate if you can fill and process the table in separate steps. For example, you can fill a standard table by appending records and then sort it. If you then use key access with the binary search option (BINARY), the response time is in logarithmic relation to the number of table entries.

Sorted Internal Tables:

Sorted tables are always saved correctly sorted by key. They also have a linear key, and, like standard tables, you can access them using either the table index or the key. When you use the key, the response time is in logarithmic relationship to the number of table entries, since the system uses a binary search. The key of a sorted table can be either unique, or non-unique, and you must specify either UNIQUE or NON-UNIQUE in the table definition. Standard tables and sorted tables both belong to the generic group index tables.

This table type is particularly suitable if you want the table to be sorted while you are still adding entries to it. You fill the table using the (INSERT) statement, according to the sort sequence defined in the table key. Table entries that do not fit are recognised before they are inserted. The response time for access using the key is in logarithmic relation to the number of table entries, since the system automatically uses a binary search. Sorted tables are appropriate for partially sequential processing in a LOOP, as long as the WHERE condition contains the beginning of the table key.

Hashed Internal Tables:

Hashes tables have no internal linear index. You can only access hashed tables by specifying the key. The response time is constant, regardless of the number of table entries, since the search uses a hash algorithm. The key of a hashed table must be unique, and you must specify UNIQUE in the table definition.

This table type is particularly suitable if you want mainly to use key access for table entries. You cannot access hashed tables using the index. When you use key access, the response time remains constant, regardless of the number of table entries. As with database tables, the key of a hashed table is always unique. Hashed tables are therefore a useful way of constructing and

using internal tables that are similar to database tables.

Regards,

Priyanka.

Former Member · ‎2007 Aug 02

hi guru..

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.

Go thru this link plzz

http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21ecf9446011d189700000e8322d00/frameset.htm

- 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.

Please have a look at below link. It will help you.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ed06446011d189700000e8322d00/frameset.htm

for more detailed info look on:

http://www.sap-img.com/abap/what-is-the-different-types-and-usage-of-views.htm

&

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/home/abap+dictionary&;

1.Go to se11

2. select view radiobutton and give a name

3. Create

4. select type of view you want to create. Such as database view.

5. give short description

6. give a table name such as mara

7. press the pushbutton relationship. here you will find all the tables which are allowed to create view with mara.

8. select one or mane tables.

8 copy

9.save , check and activate.

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.

hope it helps...

Former Member · ‎2007 Aug 02