08-01-2007 10:13 AM
Hi All
I have a requirement to join vbep and AFPO outer join .I need to select all the fields from VBEP and AUFNR,POSNR,KDAUF,KDPOS fields from AFPO table.
Kindly let me know how to write the qury using outerjoin.
point are rewarded
Regards,
Venkat
08-01-2007 10:15 AM
Hi
Check this sample code
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.
Reward all helpfull answers
Regards
Pavan
08-01-2007 10:16 AM
SELECT fieldlist
INTO CORRESPONDING FIELDS OF TABLE internal_table
FROM vbep LEFT JOIN afpo
ON vbep~fields = afpo~fields
WHERE conditions.
08-01-2007 10:16 AM
Hi
Join the VBEP and AFPO tables as below
VBEP-VBELN = AFPO-KDAUF
VBEP-POSNR = AFPO-KDPOS
and fetch the data
llok at the Joins doc and write the query
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.
On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.
AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.
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 language elements may not be used: BETWEEN, 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.
Resulting set for inner join
The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.
Resulting set for outer join
The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.
Example
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 ccarrname pconnid f~fldate
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ( ( scarr AS c
INNER JOIN spfli AS p ON pcarrid = ccarrid
AND p~cityfrom = p_cityfr
AND p~cityto = p_cityto )
INNER JOIN sflight AS f ON fcarrid = pcarrid
AND fconnid = pconnid ).
LOOP AT itab INTO wa.
WRITE: / wa-fldate, wa-carrname, wa-connid.
ENDLOOP.
Example
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 scarrid scarrname p~connid
INTO CORRESPONDING FIELDS OF TABLE itab
FROM scarr AS s
LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid
AND p~cityfrom = p_cityfr.
LOOP AT itab INTO wa.
IF wa-connid = '0000'.
WRITE: / wa-carrid, wa-carrname.
ENDIF.
ENDLOOP.
<b>Reward points for useful Answers</b>
Regards
Anji
Message was edited by:
Anji Reddy Vangala
08-01-2007 10:29 AM
Hi All,
My req is to select all the fields(*) from VBEP not individula fileds(fields list).
But it is giving syntax error if i use * in the query
I tried select vbep~*, select , select vbep but didnt work.
Please let me know the solution.
Points will be rewarded.
-Venkat
08-01-2007 10:33 AM
You cannot use the * in a JOIN. Being honest, you would NOT use it in any SQL if you don't need really ALL the fields. Otherwise, you are using too much unneeded resources, more memory, more network traffic and so.
Think you will need to write those fields once only
And if you really need ALL those fields, go to the SE11, and use CtlY CtlC to get the fields and paste them in your SQL
08-01-2007 10:36 AM
Another way to accomplish it would be the use of TWO internal tables.
SELECT *
INTO TABLE it_vbep
FROM VBEP
WHERE conditions.
CHECK sy-subrc = 0. "It will prevent a full return in the FAE
SELECT field_list
INTO CORRESPONDING FIELDS OF TABLE it_afpo
FROM afpo
FOR ALL ENTRIES IN it_vbep
WHERE it_vbep_conditions
AND additional_conditions.
If you want all the data in a single internal table, you can just LOOP the first one, using READ TABLE for the second one, and APPEND a merging workarea into a third internal table
Good luck.
08-01-2007 10:25 AM
Hi,
Join VBEP-VBELN = AFPO-KDAUF
VBEP-POSNR = AFPO-KDPOS
Outer Joins, even if there is not common value for that field on which we r joining, the records will be fetched. For the table row that doesnt have the value, NULL will be inserted. If u want NULL to be inserted on the table on the Left, use Left Outer Join and if u want NULL to be inserted for absent values in the right, use right outer Join
below link will help you.
http://help.sap.com/saphelp_erp2004/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm
http://help.sap.com/saphelp_erp2004/helpdata/en/f1/713c3b35703079e10000000a114084/frameset.htm
Regards