inner joins it is comming incorrect where condition

[Former Member]

Hi,

can any one help me inner joins it is comming incorrect where condition

REPORT ZJSUTEST1 .

INFOTYPES: 0006,

0001.

DATA : BEGIN OF ITAB occurs 0,

PERNR LIKE PA0006-PERNR,

begda like pa0006-begda,

endda like pa0006-endda,

SUBTY LIKE PA0006-SUBTY,

LAND1 LIKE PA0006-LAND1,

TELNR LIKE PA0006-TELNR,

STATE LIKE PA0006-STATE,

PAYARA LIKE PA0001-ABKRS,

COMCODE LIKE PA0001-BUKRS,

name like pa0001-ename,

END OF ITAB.

PARAMETERS : P_PERNR LIKE PA0006-PERNR,

P_BEGDA LIKE PA0006-BEGDA,

P_ENDDA LIKE PA0006-ENDDA,

P_COMCOD LIKE PA0001-BUKRS,

P_PAYARA LIKE PA0001-ABKRS.

START-OF-SELECTION.

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

WHERE A~PERNR = p_PERNR .

LOOP AT ITAB.

WRITE : / ITAB-PERNR,

ITAB-BEGDA,

ITAB-ENDDA,

ITAB-NAME,

ITAB-SUBTY,

ITAB-LAND1,

ITAB-PAYARA,

ITAB-PAYARA,

ITAB-COMCODE.

ENDLOOP.

Thanks & Regards

Kavitha

[Former Member]

Hi!

in the select:

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

WHERE A~PERNR = p_PERNR .

you must need to apply the condition of the inner join, ie:

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

<b>ON b_{field = a}field

AND b_{field2 = a}field</b>

WHERE A~PERNR = p_PERNR .

Regards,

John.

[Former Member]

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

<b>on a_{pernr = b}pernr</b>

WHERE A~PERNR = p_PERNR .

[Former Member]

Hi Kutti Pambi,

Use the SELECT stmt. as follows :

<b>SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

on a_{****** = b}***********

WHERE A~PERNR = p_PERNR .</b>

Hope this resolves your query.

Regards

Nagaraj

[Former Member]

Hi,

Still it is comming error all b_{pernr b}payara b_{comcode b}ename all are unknown.please kindly help me out.

START-OF-SELECTION.

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE b}pernr b_{payara b}comcode b~ename

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B on a_{pernr = b}pernr

WHERE A~PERNR = p_PERNR .

Thanks & Regards

Kavitha

[Former Member]

Hi

There is no field payara or comcode in PA0001. It would be abkrs - payroll area and BUKRS - Company code.

You need to know what b~payara means, It means from table PA0001 which is aliased as "b", you need to get the field payara which does not exists.

Regards

Navneet

[Former Member]

Hi Kavitha,

Here in SELECT stmt. do not use <b>b~pernr</b>. And also check whether the other keys used exist in the respective DB tables.

Hope this resolves your query.

Regards

Nagaraj

[Former Member]

Hi,

Thanks it is working.but one more problem is there .from T591S-STEXT status i have to display but no comman fields are there in this pa0006 how i have to display can any one help me out(pa0006 and pa0001 pernr is there but in T591S it is not there) .

Thanks & Regards

Kavitha

[Former Member]

Hi kutti,

there is a common field SUBTY (subtype) in both tables T591Sand PA0006, so by using this field u can get name of the subtype by using for all entires in or joins , but better to use for all ent....

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

on a_{pernr = b}pernr

WHERE A~PERNR = p_PERNR .

if sy-subrc = 0.

sort itab by pernr.

select subty stext

from T591S

INTO table tbl_t591s

for all entries in itab

where subty = itab-subtype.

if sy-subrc= 0.

sort tbl_t590s by subty.

endif.

endif.

Note : in above select statement try to avoid into corresponding field state by declaring internal tables with specified feilds in select stat bec it couses perforamance isssue.

<b>Reward with points if helpful.</b>

Regards,

Vijay

[Former Member]

Hi Vijay,

It is really helped me.But stext how i will display.and one more thing it is not displaying the text (active or not).Feild validations for pernr begda endda and screen validation.

Thanks & regards

Kavitha

[Former Member]

Hi,

Use the following code to solve your issue

START-OF-SELECTION.

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE B}PAYARA B_{COMCODE B}name

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

-

---

on a_{pernr = b}payara

-

---

now here apply the where condition

[Former Member]

Hi,

it is saying wrong select statment can any one help me out.

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE b}ename B_{pernr b}ABKRS b_{BUKRS b}ename

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

on a_{pernr = b}pernr

select stext from t591s

INTO table itab1

for all entries in itab

WHERE A~PERNR = PERNR

and subty = subty.

Thanks & Regards

Kavitha

[Former Member]

Hi

Copy and paste the below stuff.

DATA:

begin of itab1,

subty like t591s-subty,

stext like t591s-stext,

end of itab1.

SELECT A_{PERNR A}BEGDA A_{ENDDA A}SUBTY A_{LAND1 A}TELNR A_{STATE b}ename B_{pernr b}ABKRS b_{BUKRS b}ename

INTO CORRESPONDING FIELDS OF TABLE itab

FROM PA0006 AS A

INNER JOIN PA0001 AS B

on a_{pernr = b}pernr.

select subty stext from t591s

INTO table itab1

for all entries in itab

WHERE sprsl = sy-langu

and infty = '0006'

and subty = itab-subty.

Now you have subtype field decription in the itab1 internal table.

Just read this table with key subty and get the description.

Regards

Navneet

PS: Reward points if helpful