Give the syntax of Inner,outer Join?"
- 7 Answers
- 31022 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 the BAPI_CUSTMATINFO_GETDETAILM used for? what is the input and output of this bapi.
How would you suppress the display of a parameter on the selection screen?
Which function module reads the data for particular infotype ? : abap hr
What is the significance of technical settings (specified while creating a table in the data dictionary) ?
correct all performance problem without changing any functionality REPORT z_assignment_01. DATA: gt_user TYPE TABLE OF usr02 WITH HEADER LINE, gt_user_role TYPE TABLE OF agr_users WITH HEADER LINE, ********** DO NOT CHANGE THIS SECTION ********** g_begintime TYPE i, g_endtime TYPE i, g_tottime TYPE i. DATA: BEGIN OF gt_output OCCURS 0, bname LIKE usr02-bname, name_text LIKE adrp-name_text, agr_name LIKE agr_users-agr_name, text LIKE agr_texts-text, role_count TYPE i, END OF gt_output. TYPE-POOLS: slis. START-OF-SELECTION. GET RUN TIME FIELD g_begintime. ********** END NO CHANGE ********** SELECT * INTO TABLE gt_user FROM usr02 WHERE class > 'E'. SELECT * INTO TABLE gt_user_role FROM agr_users FOR ALL ENTRIES IN gt_user WHERE uname = gt_user-bname. PERFORM combine_tables. PERFORM get_addl_data. ********** DO NOT CHANGE THIS SECTION ********** GET RUN TIME FIELD g_endtime. g_tottime = ( g_endtime / 1000000 ) - ( g_begintime / 1000000 ). MESSAGE s398(00) WITH g_tottime. PERFORM output. ********** END NO CHANGE ********** *&---------------------------------------------------------------------* *& Form combine_tables *&---------------------------------------------------------------------* * Combine user and role tables *----------------------------------------------------------------------* FORM combine_tables. DATA: ls_usr21 TYPE usr21, ls_adrp TYPE adrp, ls_texts TYPE agr_texts. LOOP AT gt_user. * Get user name SELECT * INTO ls_usr21 FROM usr21 WHERE bname = gt_user-bname. SELECT * INTO ls_adrp FROM adrp WHERE persnumber = ls_usr21-persnumber. gt_output-name_text = ls_adrp-name_text. ENDSELECT. ENDSELECT. LOOP AT gt_user_role WHERE uname = gt_user-bname. * Only process roles that are currently active CHECK gt_user_role-from_dat <= sy-datum AND gt_user_role-to_dat >= sy-datum. * Get role text SELECT * INTO ls_texts FROM agr_texts WHERE agr_name = gt_output-agr_name AND spras = sy-langu AND line = 0. gt_output-text = ls_texts-text. ENDSELECT. gt_output-bname = gt_user-bname. gt_output-agr_name = gt_user_role-agr_name. APPEND gt_output. ENDLOOP. ENDLOOP. ENDFORM. " combine_tables *&---------------------------------------------------------------------* *& Form get_addl_data *&---------------------------------------------------------------------* * Get extra fields *----------------------------------------------------------------------* FORM get_addl_data. DATA: l_count TYPE i. * Count number of roles per user SORT gt_user_role BY uname. LOOP AT gt_user_role. ADD 1 TO l_count. AT END OF uname. LOOP AT gt_output WHERE bname = gt_user_role-uname. gt_output-role_count = l_count. MODIFY gt_output. CLEAR l_count. ENDLOOP. ENDAT. ENDLOOP. ENDFORM. " get_addl_data *&---------------------------------------------------------------------* *& Form output *&---------------------------------------------------------------------* * Output report *----------------------------------------------------------------------* FORM output. DATA: lt_fieldcat TYPE slis_t_fieldcat_alv, ls_fieldcat TYPE slis_fieldcat_alv, l_repid LIKE sy-repid, ls_layout TYPE slis_layout_alv, ls_variant TYPE disvariant. l_repid = sy-repid. CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE' EXPORTING i_program_name = l_repid i_internal_tabname = 'GT_OUTPUT' i_inclname = l_repid CHANGING ct_fieldcat = lt_fieldcat EXCEPTIONS inconsistent_interface = 1 program_error = 2 OTHERS = 3. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ls_fieldcat-seltext_l = 'Role Count'. ls_fieldcat-seltext_m = 'Role Count'. ls_fieldcat-seltext_s = 'Role Count'. ls_fieldcat-reptext_ddic = 'Role Count'. MODIFY lt_fieldcat FROM ls_fieldcat TRANSPORTING seltext_l seltext_m seltext_s reptext_ddic WHERE fieldname = 'ROLE_COUNT'. ls_layout-zebra = 'X'. ls_layout-colwidth_optimize = 'X'. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING is_layout = ls_layout it_fieldcat = lt_fieldcat i_save = 'A' is_variant = ls_variant TABLES t_outtab = gt_output EXCEPTIONS program_error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. " output
Explain get pernr concept when we use logical data base? : abap hr
Explain how do you get output from idoc?
Explain buffering
It is possible to assign a local data object defined in a subroutine or function module to a field group. State true or false. : abap modularization
What is Buffering Concept? When should a Table be buffered?
What is the difference between bdc_okcode and bdc_cursor? : abap bdc
What is an authorization in sap?
What is the difference between a substructure and an append structure?
Final class
Explain about workbench request and customization requests?
