topic Re: Query Regarding Native SQL in Application Development and Automation Discussions

Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:35:01 GMT

Hello Friends,

I am having a requirement to fetch the records from 3 tables( Using inner join)

into an internal table but I am facing a difficulty that I need to fetch the records from this table with entirely different conditions and I need to use ' UNION ALL' Command to fetch the records from the database. For Eg.

I have to write two select statement....

Select <f1> <f2> <f3> <f4>

from <t1> join <t2> join <t3>

into table ITAB

where <Cond. 1>

Select <f1> <f2> <f3> <f4>

from <t1> join <t2> join <t3>

appending table ITAB

where <Cond. 2>

But this is taking two much time... So the user told me to use native SQL to combine this two statement with UNION ALL.. I am using database DB2 Can anyone help me regarding this.

Thanks,

Navneet

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:36:34 GMT

If you can provide me some code.. regarding that It will be very much helpful for me

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:41:17 GMT

HI,

Info in help is good to understand better. Below is the

same for you understanding.

... FROM tabref1 [INNER] JOIN tabref2 ON cond

Effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

Table 1 Table 2

|--|||--|

\ /

Inner Join

|--||||||||--|

Example

Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias.

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

|--|||--|

\ /

Left Outer Join

NULL

|--||||||||--|

Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID )

INNER JOIN SCARR AS C

ON F_{CARRID = C}CARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

Kishore.

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:41:41 GMT

This useful for u

Syntax

EXEC SQL [PERFORMING subr].

...

ENDEXEC.

Effect

These statements define an area in an ABAP program in which one or more Native SQL statements are to be carried out. The area between EXEC and ENDEXEC is not completely checked by the syntax check. The statements entered there are passed to the Native SQL interface and processed there as follows:

Almost all SQL statements that are valid for the addressed database system can be included between EXEC and ENDEXEC, in particular the DDL statements. These SQL statements are passed from the Native SQL interface to the database system largely unchanged. The syntax rules are specified by the database system, in particular the case sensitivity rules for database objects. If the syntax allows a separator character between individual statements, you can include several Native SQL statements between EXEC and ENDEXEC. Generally, the semicolon ( is used as the separator character.

You can also include SAP-specific Native SQL language elements between EXEC and ENDEXEC. These statements are not passed directly from the Native SQL interface to the database, but are converted appropriately. These SAP-specific language elements are::

Host variables

Statements for cursor processing

Database procedure calls

Statements for establishing database connections

All Native SQL statements bypass SAP buffering. Automatic client handling is not performed.

System fields

The statement ENDEXEC sets the system fields sy-subrc and sy-dbcnt.

sy-subrc Meaning

0 The statements between EXEC and ENDEXEC were executed successfully.

4 The statements between EXEC and ENDEXEC were not executed.

The ENDEXEC statement sets sy-dbcnt to the number of table rows processed in the last Native SQL statement.

Note

Programs with Native SQL statements are generally dependent on the database system used, so that they cannot be executed in all SAP Systems. This is especially true for the examples in this section, which was written for Informix database systems.

Example

Inserting two rows in the database table SCARR. If neither of these rows exists, sy-subrc is set to 0 by ENDEXEC and sy-dbcnt to 1. Otherwise, an exception is raised and handled.

DATA: exc_ref TYPE REF TO cx_sy_native_sql_error,

error_text TYPE string.

TRY.

EXEC SQL.

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'FF', 'Funny Flyers', 'EUR',

'http://www.ff.com');

INSERT INTO scarr

(MANDT, CARRID, CARRNAME, CURRCODE, URL)

VALUES ('000', 'EF', 'Easy Flyers', 'EUR',

'http://www.ef.com');

ENDEXEC.

CATCH cx_sy_native_sql_error INTO exc_ref.

error_text = exc_ref->get_text( ).

MESSAGE error_text TYPE 'I'.

ENDTRY.

**Please reward suitable points***

With Regards

Navin Khedikar

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:43:05 GMT

Hi,

If you should hvae provided the Open Sql code we should have corrected it.

Anyhow See the Native Sql Doc:

Reading a row from the database table SPFLI using native SQL and host variables. If a row was found, sy-subrc is set to 0; if not, it is set to 4. After INTO, the STRUCTURE addition could be specified. However, this is not necessary since wa can be statically recognized as a structure.

PARAMETERS: p_carrid TYPE spfli-carrid,

p_connid TYPE spfli-connid.

DATA: BEGIN OF wa,

cityfrom TYPE spfli-cityfrom,

cityto TYPE spfli-cityto,

END OF wa.

EXEC SQL.

SELECT cityfrom, cityto

INTO :wa

FROM spfli

WHERE carrid = :p_carrid AND connid = :p_connid

ENDEXEC.

Regards,

Anji

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 11:55:28 GMT

hello friend....

u can write the query like..

Select <f1> <f2> <f3> <f4>

from <t1> join <t2> join <t3>

into table ITAB where <Cond. 1>

union all

Select <f1> <f2> <f3> <f4>

from <t1> join <t2> join <t3>

appending table ITAB

where <Cond. 2>

if u use only union it will automatically neglect the similar records and using union all will include those recrds aswell.

try this hopefully ur problem will be solved.

thanks

vijay

Re: Query Regarding Native SQL

Former Member — Fri, 16 Mar 2007 12:14:17 GMT

This are the two select statements... And I want to join them by using UNION ALL that is a native SQL For Database DB2

EXEC SQL.

SELECT FA_{ERDAT FA}VBELN FA_{VBELV FA}POSNV PS_{WERKS PS}MATNR

AK_{BSTNK AK}VKORG KP~WADAT_IST

FROM VBFA AS FA

INNER JOIN LIPS AS PS ON PS_{VBELN = FA}VBELV AND

PS_{POSNR = FA}POSNV

INNER JOIN LIKP AS KP ON KP_{VBELN = PS}VBELN

INNER JOIN VBUK AS UK ON UK_{VBELN = KP}VBELN

INNER JOIN VBAK AS AK ON AK_{VBELN = PS}VGBEL

INTO TABLE IN_REC

WHERE FA~VBTYP_N = 'R' AND

FA~VBTYP_V = 'J' AND

FA~ERDAT = WS_DATE AND

FA~ERZET = WS_TIME AND

PS~WERKS = P_WERKS AND

AK~VKORG = P_VKORG AND

ak~auart in s_auart and

UK~WBSTK = 'C' and

kp~vstel in s_vstel.

SELECT FA_{ERDAT FA}VBELN FA_{VBELV FA}POSNV PS_{WERKS PS}MATNR

AK_{BSTNK AK}VKORG KP~WADAT_IST

FROM VBFA AS FA

INNER JOIN LIPS AS PS ON PS_{VBELN = FA}VBELV AND

PS_{POSNR = FA}POSNV

INNER JOIN LIKP AS KP ON KP_{VBELN = PS}VBELN

INNER JOIN VBUK AS UK ON UK_{VBELN = KP}VBELN

INNER JOIN VBAK AS AK ON AK_{VBELN = PS}VGBEL

INTO TABLE IN_REC

WHERE FA~VBTYP_N = 'R' AND

FA~VBTYP_V = 'J' AND

FA~ERDAT > WS_DATE AND

PS~WERKS = P_WERKS AND

AK~VKORG = P_VKORG AND

ak~auart in s_auart and

UK~WBSTK = 'C' and

kp~vstel in s_vstel.

endexec.