topic innerjoin in Application Development and Automation Discussions

innerjoin

Former Member — Wed, 14 Sep 2005 09:00:14 GMT

hi to all,

in selection option i have written like this

select mard~matnr

mard~labst

mard~speme

mard~werks

mard~lgort

mard~lgpbe

mard~kzill

s039~basme

s039~wbwbest

into corresponding fields of table itabp from mard

inner join s039 on

mard_{matnr = s039}matnr

where mard~matnr in s_matnr and

s039~basme in s_basme.

but im not getting the output....

can anyone tellme how...

thanks in advance

Re: innerjoin

Vinod_Chandran — Wed, 14 Sep 2005 09:03:07 GMT

Hi Raju,

Can you try giving alias like given below?

into corresponding fields of table itabp from mard as mard

inner join s039 as s039 on

mard_{matnr = s039}matnr

Thanks

Vinod

Re: innerjoin

Former Member — Wed, 14 Sep 2005 09:03:17 GMT

seems like ur code is correct..

do u have proper data in table ?

regards

Re: innerjoin

Former Member — Wed, 14 Sep 2005 09:08:40 GMT

here is my entire code

REPORT ZMMGTB111 .

****************************INTERNAL TABLES**************************

***********************************************************************

tables: MARD, S039."LOGON DATA

**********************************DATA********************************

************************************************************************

data: begin of itabp occurs 0,

matnr type mard-matnr, "MATERIAL NO

labst type mard-labst, "VALUATED STOCK

speme type mard-speme, "BLOCKED STOCK

werks type mard-werks, "PLANT

lgort type mard-lgort, "SALES ORGANISATION

lgpbe type mard-lgpbe, "STORAGE BIN

kzill type mard-kzill, "PHYSICAL INVENTORY

umlme type mard-umlme, "Stock in transfer

insme type mard-insme, "Stock in Quality Inspection

einme type mard-einme, "Total stock of all related batches

retme type mard-retme, "Blocked stock returns

wbwbest type s039-wbwbest, "Vlalue of stock

basme TYPE s039-basme, "UNIT

end of itabp.

**********************SELECTION SCEREEN*******************************

************************************************************************

selection-screen begin of block b1 with frame title text-010.

select-options:

s_matnr for mard-matnr modif id sc1,

s_basme for s039-basme no-extension no intervals.

selection-screen end of block b1.

selection-screen begin of block b2 with frame title text-020.

**************************RADIO BUTTONS*******************************

************************************************************************

parameter: r2 radiobutton group g1 modif id sc1,

r3 radiobutton group g1,

r4 radiobutton group g1.

selection-screen end of block b2.

selection-screen begin of block b3 with frame title text-030.

*****************************CHECK BOX********************************

************************************************************************

parameter: C1 as checkbox.

selection-screen end of block b3.

data: lv_labst like mard-labst.

*********************RADIO BUTTION R2 WRITE STATEMENT****************

*************************************WITH STORAGE BIN SELECTION******

top-of-page.

IF R2 EQ 'X'.

format intensified.

write: 8 'Material' color 3 ,

26 'Valuated Stock' color 7,

46 'Blocked Stock' color 5,

65 'UNIT' color 1,

76 'Plant' color 6,

84 'St Loca' color 3,

96 'St Bin' color 7,

106 'St VALUE' color 5.

106 sy-vline.

endif.

************************RADIO BUTTION R3 WRITE STATEMENT *************

**********************************WITH OUT STORAGE BIN SELECTION******

IF R3 EQ 'X'.

format intensified.

write: 8 'Material' color 3 ,

26 'Valuated Stock' color 7,

46 'Blocked Stock' color 5,

65 'UNIT' color 1,

76 'Plant' color 6,

85 'St Loca' color 3,

95 'St VALUE' color 5.

endif.

**********************RADIO BUTTION R4 WRITE STATEMENT ****************

STOCK DETAILS ***************

IF R4 EQ 'X'.

format intensified.

write: 8 'Material' color 3 ,

25 'Valuated Stock' color 7,

45 'Blocked Stock' color 5,

63 'UNIT' color 1,

74 'St In Trf' color 6,

88 'St in QUL' color 3,

104 'St Ris Bat' color 7,

106 'St VALUE' color 5.

106 sy-vline.

endif.

uline (143).

start-of-selection.

SELECTION STATEMENT *****************

RADIO BUTTION R2 *************

WITH STORAGE BIN SELECTION ******

IF R2 EQ 'X'.

***********************check box in write statement*******************

IF c1 EQ 'X'.

write: 120 'Inventory' color 1.

ENDIF.

format intensified off.

select mard~matnr

mard~labst

mard~speme

mard~werks

mard~lgort

mard~lgpbe

mard~kzill

s039~basme

s039~wbwbest

into corresponding fields of table itabp from mard

inner join s039 on

mard_{matnr = s039}matnr

where mard~matnr in s_matnr and

s039~basme in s_basme.

loop at itabp.

format color 2.

if sy-subrc = 0.

SKIP.

***************************************TO GET TOTAL STCOK*************

LV_labst = LV_labst + ITABP-labst.

******************************************WRITE STATEMENT*************

write: /10 ITABP-matnr , " MATERIAL

AT 20 itabp-labst , " VALUATED STOCK

AT 40 itabp-speme , "BLOCKED STOCK

AT 66 itabp-basme , "UNIT

AT 76 itabp-werks , "PLANT

AT 86 itabp-lgort , "SALES ORGANISATION

AT 96 itabp-lgpbe , "STORAGE BIN

AT 108 itabp-wbwbest. "STOCK VALUE

*********************************CHECK BOX IN WRITE STATEMENT*********

IF c1 EQ 'X'.

format color 2.

write: 122 itabp-kzill. "PHYSICAL INVENTORY

format color off.

ENDIF.

endif.

format color off.

ENDLOOP.

ENDIF.

SELECTION STATEMENT *****************

RADIO BUTTION R3 *************

WITH OUT STORAGE BIN SELECTION ******

IF R3 EQ 'X'.

*******************************CHECK BOX IN HEADING WRITE STATEMENT ****

IF c1 EQ 'X'.

write: 110 'Inventory' color 1.

ENDIF.

format intensified off.

select mard~matnr

mard~labst

mard~speme

mard~werks

mard~lgort

mard~lgpbe

mard~kzill

s039~wbwbest

s039~basme

into corresponding fields of table itabp from mard as mard

inner join s039 as s039 on

mard_{matnr = s039}matnr

where mard~matnr in s_matnr and

s039~basme in s_basme.

loop at itabp.

format color 2.

if sy-subrc = 0.

SKIP.

***************************************TO GET TOTAL STCOK*************

LV_labst = LV_labst + ITABP-labst.

******************************************WRITE STATEMENT*************

write: /10 ITABP-matnr , "MATERIAL NO

20 itabp-labst , "VALUATED STOCK

40 itabp-speme , "BLOCKED STOCK

66 itabp-basme , "UNIT

76 itabp-werks , "PLANT

88 itabp-lgort , "SALES ORGANISATION

98 itabp-wbwbest."STOCK VALUE

*********************************CHECK BOX IN WRITE STATEMENT*********

IF c1 EQ 'X'.

format color 2.

write: 112 itabp-kzill. "PHYSICAL INVENTORY

format color off.

ENDIF.

format color off.

ENDLOOP.

ENDIF.

SELECTION STATEMENT *****************

RADIO BUTTION R4 *************

STOCK DETAILS *************

IF R4 EQ 'X'.

***********************check box in write statement*******************

IF c1 EQ 'X'.

write: 120 'Inventory' color 1.

ENDIF.

format intensified off.

select mard~matnr

mard~labst

mard~speme

mard~werks

mard~lgort

mard~lgpbe

mard~kzill

mard~umlme

mard~insme

mard~einme

mard~retme

s039~wbwbest

s039~basme

into corresponding fields of table itabp from s039

inner join mard on

s039_{matnr = mard}matnr

where mard~matnr in s_matnr and

s039~basme in s_basme.

loop at itabp.

format color 2.

if sy-subrc = 0.

SKIP.

***************************************TO GET TOTAL STCOK*************

LV_labst = LV_labst + ITABP-labst.

******************************************WRITE STATEMENT*************

write: /10 ITABP-matnr , "MATERIAL NO

20 itabp-labst , "VALUATED STOCK

40 itabp-speme , "BLOCKED STOCK

66 itabp-basme , "UNIT

76 itabp-umlme , "Stock in transfer

86 itabp-insme , "Stock in Quality Inspection

96 itabp-einme , "Total stock of all related batches

108 itabp-wbwbest. "STOCK VALUE

*********************************CHECK BOX IN WRITE STATEMENT*********

IF c1 EQ 'X'.

format color 2.

write: 122 itabp-kzill. "PHYSICAL INVENTORY

format color off.

ENDIF.

endif.

format color off.

ENDLOOP.

ENDIF.

**************************************SCREEN TEXT INTENSIFIED********

LOOP AT SCREEN.

IF SCREEN-GROUP1 = 'SC1'.

SCREEN-INTENSIFIED = '1'.

MODIFY SCREEN.

CONTINUE.

ENDIF.

IF SCREEN-GROUP1 = 'SC2'.

SCREEN-INTENSIFIED = '0'.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

***********************************************COLUMN TOTAL***********

write: /09 'TOTAL STOCK' color 1.

write: 20 lv_LABST color 1.

Re: innerjoin

Former Member — Wed, 14 Sep 2005 09:11:01 GMT

What is teh SY-SUBRC & SY_DBCNT just after select .

Check in debugging.

Re: innerjoin

Former Member — Wed, 14 Sep 2005 09:17:25 GMT

Raju,

Ur Code looks good.

I am feeling that there are no common records present in the MARD and S039 tables.

So try checking the Execution in the Debugging!!!!

and let us know..

Thanks

Kam

Re: innerjoin

Former Member — Wed, 14 Sep 2005 09:45:33 GMT

Hi,

Check in the table whether any entries exist for the specified selection creteria.

Cheers,

Sasi

Re: innerjoin

Former Member — Wed, 14 Sep 2005 10:35:09 GMT

yes i checked...

so39 table is not exit in the library...

thanks to all..

raju.