02-22-2012 6:55 AM
Hi everyone,
It does not gives any error, but it is not fetching any values...but when i dont include fourth table CSKT AND THE FIELD LTEXT it is fetching the values.
SELECT SINGLE
T1~PERNR
T1~BEGDA
T1~ENAME
T1~PLANS
T1~KOSTL
T2~STRAS
T2~PSTLZ
T2~ORT01
T3~PLSTX
T4~LTEXT
INTO FS_TAB
FROM PA0001 AS T1
INNER JOIN PA0006 AS T2 ON T1PERNR eq T2PERNR
INNER JOIN T528T AS T3 ON T1PLANS EQ T3PLANS
INNER JOIN CSKT AS T4 ON T1KOSTL EQ T4KOSTL
WHERE T1~PERNR eq p_pernr.
02-22-2012 7:14 AM
Hi, Vasanthakumar.
Try to use
LEFT OUTER JOIN CSKT ...
Using OUTER JOIN, if even CSKT table is empty it will not reduce the number of selected records.
Hope it will be helpful for you.
Best regards,
George Shlyahov.
02-22-2012 7:18 AM
Hi Vasanthakumar,
You Inner join is not fetching any values, may be because there are no records for the combination,
Try excluding CSKT AND THE FIELD LTEXT & Use for all entries from the CSKT table w.r.t. the records you get in the inner join statement.
02-22-2012 7:40 AM
02-22-2012 7:53 AM
Hello Vasanth,
I would suggest you not to use inner join in any of your queries. Try using the FOR ALL ENTRIES. But in case of using for all entries follow all the prerequisites for that,
1). Sort the table first.
2). Check if it is not initial.
3). Copy it to temporary table and remove adjacent duplicates.
If the table you use in For all entries is empty then it will fetch all the records from the table for the query written. i.e., there is no point in using for all entries of an empty table.
Coming to your question,
You are not getting any results because one of your table in your joins doesn't have any records meeting your selection criteria.
Please let me know for more details.
Regards,
Praveenkumar T.
02-22-2012 8:11 AM
> I would suggest you not to use inner join in any of your queries. Try using the FOR ALL ENTRIES.
What for it is so categorical?
INNER JOIN is very useful construction and it should be implemented if it is necessary (up to 3 times in one select it is normal).
It already was discussed repeatedly. To avoid holy war, check this thread:
[http://forums.sdn.sap.com/thread.jspa?threadID=996959&tstart=0]
Best regards,
George Shlyahov.
02-22-2012 8:35 AM
Before i saw your response, i found that the values are missing for the field KOSTL in table PA0001. Thx for ur response.
02-22-2012 12:49 PM
A little off-topic, let me suggest to drop those aliases, the code is more difficult to read, better use the actual table names in the column identifiers. Aliases make sense (and are mandatory) when you are joining the same table several times.
Thomas
02-22-2012 8:10 AM
Change like this
SELECT SINGLE
T1~PERNR
T1~BEGDA
T1~ENAME
T1~PLANS
T1~KOSTL
T2~STRAS
T2~PSTLZ
T2~ORT01
T3~PLSTX
T4~LTEXT
INTO FS_TAB
FROM PA0001 AS T1
INNER JOIN PA0006 AS T2 ON T1~PERNR eq T2~PERNR
INNER JOIN T528T AS T3 ON T1~PLANS EQ T3~PLANS
LEFT OUTER JOIN CSKT AS T4 ON T1~KOSTL EQ T4~KOSTL
WHERE T1~PERNR eq p_pernr.
OR
SELECT SINGLE
T1~PERNR
T1~BEGDA
T1~ENAME
T1~PLANS
T1~KOSTL
T2~STRAS
T2~PSTLZ
T2~ORT01
T3~PLSTX
T4~LTEXT
INTO FS_TAB
FROM PA0001 AS T1
LEFT OUTER JOIN PA0006 AS T2 ON T1~PERNR eq T2~PERNR
LEFT OUTER JOIN T528T AS T3 ON T1~PLANS EQ T3~PLANS
LEFT OUTER JOIN CSKT AS T4 ON T1~KOSTL EQ T4~KOSTL
WHERE T1~PERNR eq p_pernr.
as long as the table is not used in where condition, u can use left outter join.