Give the syntax of Inner,outer Join?"
- 7 Answers
- 30989 Views
- Genpact, HCL, Satyam, I also Faced
- E-Mail Answers
Answer Posted / shreeshail diggi
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.
The syntax of the join conditions join_cond is the same as
that of the sql_cond conditions after the addition WHERE,
with the following differences:
At least one comparison must be specified after ON.
Individual comparisons may be joined using AND only.
All comparisons must contain a column in the database table
or the view dbtab_right on the right-hand side as an
operand.
The following additions not be used: NOT, LIKE, IN.
No sub-queries may be used.
For outer joins, only equality comparisons (=, EQ) are
possible.
If an outer join occurs after FROM, the join condition of
every join expression must contain at least one comparison
between columns on the left-hand and the right-hand side.
In outer joins, all comparisons that contain columns as
operands in the database table or the view dbtab_right on
the right-hand side must be specified in the corresponding
join condition. In the WHERE condition of the same SELECT
command, these columns are not allowed as operands
Example INNER JOIN :
Join the columns carrname, connid, fldate of the database
tables scarr, spfli and sflight by means of two inner
joins. A list is created of the flights from p_cityfr to
p_cityto. Alternative names are used for every table.
PARAMETERS: p_cityfr TYPE spfli-cityfrom,
p_cityto TYPE spfli-cityto.
DATA: BEGIN OF wa,
fldate TYPE sflight-fldate,
carrname TYPE scarr-carrname,
connid TYPE spfli-connid,
END OF wa.
DATA itab LIKE SORTED TABLE OF wa
WITH UNIQUE KEY fldate carrname connid.
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 ).
LOOP AT itab INTO wa.
WRITE: / wa-fldate, wa-carrname, wa-connid.
ENDLOOP.
Example OUTER JOIN:
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.
Thank's and Regards,
Shreeshail Diggi
SAP Technical Consultant Infinite India
| Is This Answer Correct ? | 12 Yes | 1 No |
Post New Answer View All Answers
What is a sequence of event-triggered in report?
What is the difference between rfc and bapi function modules? : abap bdc
What is ole?
Can you create a table with fields not referring to data elements? : abap data dictionary
What is synchronous update?
What is the use of a pretty printer?
If I have put commit in badi implementation class after update, is it allowed? Also if some standard functionality already implemented an update and now i m doing update and if it fails, what will be the impact of commit?
What are the functions of lock objects?
how can we use the text randomly or circularly in smartforms.... means... suppose i have 'abap' horizantally... but i want it in vertically... how can... plz any body tell me this....
What is a fall back class?
What is a transaction variant and why are they used?
Does every abap/4 have a modular structure?
What is asynchronous update?
Is there any BAPI to retrieve list of customers and their sales areas i.e., Cust No, Distr Channel and Division for all the customers.
What are different data types in abap/4?
