topic Re: Inner join query not working properly in Application Development and Automation Discussions

Inner join query not working properly

Former Member — Wed, 22 Feb 2012 06:55:53 GMT

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 T1_{PERNR eq T2}PERNR

INNER JOIN T528T AS T3 ON T1_{PLANS EQ T3}PLANS

INNER JOIN CSKT AS T4 ON T1_{KOSTL EQ T4}KOSTL

WHERE T1~PERNR eq p_pernr.

Re: Inner join query not working properly

Former Member — Wed, 22 Feb 2012 07:14:36 GMT

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.

Re: Inner join query not working properly

SharathYaralkattimath — Wed, 22 Feb 2012 07:18:13 GMT

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.

Re: Inner join query not working properly

Former Member — Wed, 22 Feb 2012 07:40:32 GMT

This message was moderated.

Re: Inner join query not working properly

Former Member — Wed, 22 Feb 2012 07:53:58 GMT

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.

Re: Inner join query not working properly

surajarafath — Wed, 22 Feb 2012 08:10:24 GMT

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.

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.

Re: Inner join query not working properly

Former Member — Wed, 22 Feb 2012 08:11:00 GMT


>         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.

Re: Inner join query not working properly

Former Member — Wed, 22 Feb 2012 08:35:37 GMT

Before i saw your response, i found that the values are missing for the field KOSTL in table PA0001. Thx for ur response.

Re: Inner join query not working properly

ThomasZloch — Wed, 22 Feb 2012 12:49:00 GMT

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