Views

[Former Member]

Hi All,

I need a view with outer join. How to do this.

[prasanth_kasturi]

hi,

you can go with maintenance view or helpview

Help View

You have to create a help view if a view with outer join is needed as selection method of a search help.

The selection method of a search help is either a table or a view. If you have to select data from several tables for the search help, you should generally use a database view as selection method. However, a database view always implements an inner join. If you need a view with outer join for the data selection, you have to use a help view as selection method.

PROCEDURE

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

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

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

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

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

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

7)Choose Acitivate

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

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

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.

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

REWARD IF HELPFUL

Prasanth

[Former Member]

Hi,

U can create a helpview or maintenance view for this.

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.

Procedure for Helpview.

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.

Procedure for maintenance view.

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.

Regards,

Jagadish

[Former Member]

EXECPT DATABASE VIEW ANY VIEW U CREATE GIVES U A OUTER JOIN

[Former Member]

Hi,

Outer joins basically copnsists of the conpect of union of the set theory .

here you go with an example from SAP documents :

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

Also check http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm

there are two ways : 1. outer join with db tab and 2. with itab.

For db tab:

SELECT - join

Syntax

... [(] {dbtab_left AS tabalias_left} | join

{INNER JOIN}|{LEFT OUTER JOIN}

{dbtab_right AS tabalias_right ON join_cond} [)] ... .

Effect

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of INNER JOIN or LEFT OUTER JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

e.g.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

For Itab.:

SELECT - cond

Syntax

... FOR ALL ENTRIES IN itab WHERE sql_cond ... .

Effect:

The addition WHERE restricts the number of lines included in the result set by the statement SELECT, by using a logical expression sql_cond. The logical expression compares the content of columns in the database with the content of ABAP data objects, or with the content of other columns. You can use the optional addition FOR ALL ENTRIES to compare the content of a column in the database with a component with all lines of a structured internal table itab.

The logical expression sql_cond is either true, false, or unknown. The expression is unknown if one of the columns involved in the database contains a null value and is evaluated with another comparison as IS NULL. A line is only included in the resulting set if the logical expression is true.

Except for columns of type STRING or RAWSTRING, all columns of the database tables or views listed after FROM can be evaluated after the WHERE condition. The columns do not necessarily have to be a part of the resulting set.

Regards,

Raj.