topic Re: Help on Inner Join Query in Application Development and Automation Discussions

Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 11:56:37 GMT

Hi ABAPers,

I was trying to develop a report for which query is something like this.

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

&----

*& Report ZT_SERIAL

&----

REPORT ZT_SERIAL.

TABLES : SER01,SER03,OBJK,LIKP,LIPS. " LIKP - SD invoice header table, LIPS - SD invoice details table

TYPE-POOLS: SLIS.

DATA: AFIELD TYPE SLIS_FIELDCAT_ALV.

DATA: FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.

MANDATORY WHILE DECLARING ALV ***

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV..

DATA: V_EVENTS TYPE SLIS_T_EVENT.

DATA: LAYOUT TYPE SLIS_LAYOUT_ALV.

DATA: P_FIELDTAB TYPE SLIS_T_FIELDCAT_ALV.

DATA: I_TITLE_SL_NO_TRACK TYPE LVC_TITLE VALUE 'SERIAL NO TRACKER'.

DATA : BEGIN OF ITAB OCCURS 0,

OBKNR LIKE OBJK-OBKNR, "OBJECT LIST

OBZAE LIKE OBJK-OBZAE, "OBJECT COUNTER

KUNDE LIKE SER01-KUNDE, "CUSTOMER NO

MATNR LIKE OBJK-MATNR, "PART NO

SERNR LIKE OBJK-SERNR, "SERIAL NO

MBLNR LIKE SER03-MBLNR, "GRN/MATERIAL DOC NO

LIEF_NR LIKE SER01-LIEF_NR,"OUTBOUND DELV NO

DATUM LIKE SER01-DATUM, "OUTBOUND DELV DATE

TASER LIKE OBJK-TASER, "HEADER TABLE

ANZSN LIKE SER01-ANZSN, "NO OF SERIAL NOS

VGBEL LIKE LIPS-VGBEL, "SALES ORDER NO

END OF ITAB.

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_KUNDE FOR SER01-KUNDE,

S_VGBEL FOR LIPS-VGBEL,

S_MATNR FOR OBJK-MATNR,

S_SERNR FOR OBJK-SERNR,

S_MBLNR FOR SER03-MBLNR,

S_LIFNR FOR SER01-LIEF_NR,

S_DATUM FOR SER01-DATUM.

SELECTION-SCREEN : END OF BLOCK B1.

PERFORM TRACKING.

PERFORM FLDCAT.

PERFORM BUILD_LAYOUT.

***CALL FUNCTION TO DISPLAY IN ALV FORMAT*******

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = SY-REPID

I_GRID_TITLE = I_TITLE_SL_NO_TRACK

IS_LAYOUT = LAYOUT

IT_FIELDCAT = P_FIELDTAB[]

I_SAVE = 'A'

IT_EVENTS = V_EVENTS

TABLES

T_OUTTAB = ITAB.

***END OF CALL FUNCTION

FORM TRACKING.

SELECT OBJK_{OBKNR OBJK}OBZAE OBJK_{MATNR OBJK}SERNR OBJK~TASER

SER01_{KUNDE SER01}LIEF_NR SER01_{DATUM SER01}ANZSN

SER03~MBLNR

LIPS~VGBEL

INTO TABLE ITAB FROM OBJK

INNER JOIN SER01 ON OBJK_{OBKNR = SER01}OBKNR

INNER JOIN SER03 ON OBJK_{OBKNR = SER03}OBKNR

INNER JOIN LIPS ON SER01_{LIEF_NR = LIPS}VBELN

WHERE

OBJK_{TASER = 'SER01' OR OBJK}TASER = 'SER03'.

OBJK~MATNR IN S_MATNR AND

OBJK~SERNR IN S_SERNR AND

SER01~KUNDE IN S_KUNDE AND

SER01~LIEF_NR IN S_LIFNR AND

SER01~DATUM IN S_DATUM AND

SER03~MBLNR IN S_MBLNR AND

LIPS~VGBEL IN S_VGBEL.

ENDFORM.

&----

*& Form fldcat

&----

text

----

FORM FLDCAT.

PERFORM FLDCAT1 USING 'KUNDE' 'Customer No'.

PERFORM FLDCAT1 USING 'VGBEL' 'Sales Order No'.

PERFORM FLDCAT1 USING 'MATNR' 'Tejas Part No'.

PERFORM FLDCAT1 USING 'SERNR' 'Serial No'.

PERFORM FLDCAT1 USING 'MBLNR' 'Material Doc No'.

PERFORM FLDCAT1 USING 'LIEF_NR' 'Outbound Delv No'.

PERFORM FLDCAT1 USING 'DATUM' 'Outbound Delv Date'.

ENDFORM. "fldcat

&----

*& Form fldcat1

&----

text

----

-->FNAM text
-->FTEXT text

----

FORM FLDCAT1 USING FNAM FTEXT.

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV.

L_FIELDCAT-FIELDNAME = FNAM.

L_FIELDCAT-SELTEXT_M = FTEXT.

IF FNAM EQ 'MATNR'."OR fnam EQ 'PKUNAG'.

L_FIELDCAT-KEY = 'X'.

ENDIF.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

APPEND L_FIELDCAT TO P_FIELDTAB.

CLEAR fldcat1.

ENDFORM. "fldcat1

&----

*& Form build_layout

&----

text

----

FORM BUILD_LAYOUT .

LAYOUT-NO_INPUT = 'X'.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

LAYOUT-ZEBRA = 'X'.

LAYOUT-TOTALS_TEXT = 'Totals'(256).

layout-coltab_fieldname = 'CELL_COLOUR'.

*clear layout.

ENDFORM.

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

But when I run this query,the program gets terminated.Can you please help me in rectifying the query and generate the output?

Thanks in advance

Bhavin

Re: Help on Inner Join Query

former_member188827 — Tue, 11 Mar 2008 11:59:01 GMT

wats da message u get?

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:03:37 GMT

hi abapuser,

The Program gets terminated and gives message like "The current ABAP program "ZT_SERIAL" had to be terminated because it has come across a statement that unfortunately cannot be executed. "

You can copy this program and execute it for checking the exact error.

Thank you,

Bhavin

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:14:51 GMT

The data read during a SELECT access could not be inserted into the target field. Either conversion is not supported for the target field's type or the target field is too short to accept the value or the data are not in a form that the target field can accept .

Also Change the Query as Follows:

SELECT OBJK_{OBKNR OBJK}OBZAE OBJK_{MATNR OBJK}SERNR OBJK~TASER

SER01_{KUNDE SER01}LIEF_NR SER01_{DATUM SER01}ANZSN

SER03~MBLNR

LIPS~VGBEL

INTO CORRESPONDING FIELDS OF TABLE ITAB FROM OBJK as objk

INNER JOIN SER01 as ser01

ON OBJK_{OBKNR = SER01}OBKNR

INNER JOIN SER03 as ser03

ON OBJK_{OBKNR = SER03}OBKNR

INNER JOIN LIPS as lips

ON SER01_{LIEF_NR = LIPS}VBELN

WHERE

OBJK_{TASER = 'SER01' OR OBJK}TASER = 'SER03'.

awrd points if useful

Bhupal

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:14:56 GMT

Change ur select query to

SELECT OBJK_{OBKNR OBJK}OBZAE OBJK_{MATNR OBJK}SERNR OBJK~TASER

SER01_{KUNDE SER01}LIEF_NR SER01_{DATUM SER01}ANZSN

SER03~MBLNR

LIPS~VGBEL

INTO corresponding fields of TABLE ITAB FROM OBJK

INNER JOIN SER01 ON OBJK_{OBKNR = SER01}OBKNR

INNER JOIN SER03 ON OBJK_{OBKNR = SER03}OBKNR

INNER JOIN LIPS ON SER01_{LIEF_NR = LIPS}VBELN

WHERE

OBJK_{TASER = 'SER01' and "OR OBJK}TASER = 'SER03'

OBJK~MATNR IN S_MATNR AND

OBJK~SERNR IN S_SERNR AND

SER01~KUNDE IN S_KUNDE AND

SER01~LIEF_NR IN S_LIFNR AND

SER01~DATUM IN S_DATUM AND

SER03~MBLNR IN S_MBLNR AND

LIPS~VGBEL IN S_VGBEL.

It is because ur field sequence in internal table and the sequence in select query is different

Reward if useful

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:19:52 GMT

I have corrected the code please copy and past it and then try.

Report ZT_SERIAL.

*&----

TABLES : SER01,SER03,OBJK,LIKP,LIPS.

" LIKP - SD invoice header table, LIPS - SD invoice details table

TYPE-POOLS: SLIS.

DATA: AFIELD TYPE SLIS_FIELDCAT_ALV.

DATA: FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.

+ MANDATORY WHILE DECLARING ALV ***

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV..

DATA: V_EVENTS TYPE SLIS_T_EVENT.

DATA: LAYOUT TYPE SLIS_LAYOUT_ALV.

DATA: P_FIELDTAB TYPE SLIS_T_FIELDCAT_ALV.

DATA: I_TITLE_SL_NO_TRACK TYPE LVC_TITLE VALUE 'SERIAL NO TRACKER'.

DATA : BEGIN OF ITAB OCCURS 0,

OBKNR LIKE OBJK-OBKNR, "OBJECT LIST

OBZAE LIKE OBJK-OBZAE, "OBJECT COUNTER

KUNDE LIKE SER01-KUNDE, "CUSTOMER NO

MATNR LIKE OBJK-MATNR, "PART NO

SERNR LIKE OBJK-SERNR, "SERIAL NO

MBLNR LIKE SER03-MBLNR, "GRN/MATERIAL DOC NO

LIEF_NR LIKE SER01-LIEF_NR,"OUTBOUND DELV NO

DATUM LIKE SER01-DATUM, "OUTBOUND DELV DATE

TASER LIKE OBJK-TASER, "HEADER TABLE

ANZSN LIKE SER01-ANZSN, "NO OF SERIAL NOS

VGBEL LIKE LIPS-VGBEL, "SALES ORDER NO

END OF ITAB.

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_KUNDE FOR SER01-KUNDE,

S_VGBEL FOR LIPS-VGBEL,

S_MATNR FOR OBJK-MATNR,

S_SERNR FOR OBJK-SERNR,

S_MBLNR FOR SER03-MBLNR,

S_LIFNR FOR SER01-LIEF_NR,

S_DATUM FOR SER01-DATUM.

SELECTION-SCREEN : END OF BLOCK B1.

PERFORM TRACKING.

PERFORM FLDCAT.

PERFORM BUILD_LAYOUT.

***CALL FUNCTION TO DISPLAY IN ALV FORMAT*******

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = SY-REPID

I_GRID_TITLE = I_TITLE_SL_NO_TRACK

IS_LAYOUT = LAYOUT

IT_FIELDCAT = P_FIELDTAB[]

I_SAVE = 'A'

IT_EVENTS = V_EVENTS

TABLES

T_OUTTAB = ITAB.

***END OF CALL FUNCTION

FORM TRACKING.

*SELECT OBJK~OBKNR

OBJK~OBZAE
OBJK~MATNR
OBJK~SERNR
OBJK~TASER
SER01~KUNDE
SER01~LIEF_NR
SER01~DATUM
SER01~ANZSN
SER03~MBLNR
LIPS~VGBEL
INTO TABLE ITAB FROM OBJK
INNER JOIN SER01 ON OBJK_{OBKNR = SER01}OBKNR
INNER JOIN SER03 ON OBJK_{OBKNR = SER03}OBKNR
INNER JOIN LIPS ON SER01_{LIEF_NR = LIPS}VBELN
WHERE

*( OBJK_{TASER = 'SER01' OR OBJK}TASER = 'SER03' ) and

*OBJK-MATNR IN S_MATNR AND

*OBJK~SERNR IN S_SERNR AND

*SER01~KUNDE IN S_KUNDE AND

*SER01~LIEF_NR IN S_LIFNR AND

*SER01~DATUM IN S_DATUM AND

*SER03~MBLNR IN S_MBLNR AND

*LIPS~VGBEL IN S_VGBEL.

SELECT a~OBKNR "OBJK

a~OBZAE

a~MATNR

a~SERNR

a~TASER

b~KUNDE "SER01

b~LIEF_NR

b~DATUM

b~ANZSN

c~MBLNR "SER03

D~VGBEL "LIPS

INTO TABLE ITAB FROM ( OBJK as a

INNER JOIN SER01 as b ON a_{OBKNR = b}OBKNR

INNER JOIN SER03 as c ON a_{OBKNR = c}OBKNR

INNER JOIN LIPS as d ON b_{LIEF_NR = d}VBELN )

WHERE

( a_{TASER = 'SER01' OR a}TASER = 'SER03' ) and

a~MATNR IN S_MATNR AND

a~SERNR IN S_SERNR AND

b~KUNDE IN S_KUNDE AND

b~LIEF_NR IN S_LIFNR AND

b~DATUM IN S_DATUM AND

c~MBLNR IN S_MBLNR AND

d~VGBEL IN S_VGBEL.

ENDFORM.

*&----

*& Form fldcat

*&----

* text

*----

FORM FLDCAT.

PERFORM FLDCAT1 USING 'KUNDE' 'Customer No'.

PERFORM FLDCAT1 USING 'VGBEL' 'Sales Order No'.

PERFORM FLDCAT1 USING 'MATNR' 'Tejas Part No'.

PERFORM FLDCAT1 USING 'SERNR' 'Serial No'.

PERFORM FLDCAT1 USING 'MBLNR' 'Material Doc No'.

PERFORM FLDCAT1 USING 'LIEF_NR' 'Outbound Delv No'.

PERFORM FLDCAT1 USING 'DATUM' 'Outbound Delv Date'.

ENDFORM. "fldcat

*&----

*& Form fldcat1

*&----

text

*----

-->FNAM text
-->FTEXT text

*----

FORM FLDCAT1 USING FNAM FTEXT.

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV.

L_FIELDCAT-FIELDNAME = FNAM.

L_FIELDCAT-SELTEXT_M = FTEXT.

IF FNAM EQ 'MATNR'."OR fnam EQ 'PKUNAG'.

L_FIELDCAT-KEY = 'X'.

ENDIF.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

APPEND L_FIELDCAT TO P_FIELDTAB.

* CLEAR fldcat1.

ENDFORM. "fldcat1

*&----

*& Form build_layout

*&----

* text

*----

FORM BUILD_LAYOUT .

LAYOUT-NO_INPUT = 'X'.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

LAYOUT-ZEBRA = 'X'.

LAYOUT-TOTALS_TEXT = 'Totals'(256).

* layout-coltab_fieldname = 'CELL_COLOUR'.

*clear layout.

ENDFORM.

Regards,

Venkat

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:19:53 GMT

Hello Bhavin,

I just done indentation and added INTO CORRESPONDING FIELDS OF TABLE ITAB in your query.

no its working fine.check it.

also copying my changes below

reward points if useful,

Kota

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

TABLES : ser01,ser03,objk,likp,lips. " LIKP - SD invoice header table, LIPS - SD invoice details table

TYPE-POOLS: slis.

DATA: afield TYPE slis_fieldcat_alv.

DATA: fieldcat TYPE slis_t_fieldcat_alv.

+ mandatory while declaring alv ***

DATA: l_fieldcat TYPE slis_fieldcat_alv..

DATA: v_events TYPE slis_t_event.

DATA: layout TYPE slis_layout_alv.

DATA: p_fieldtab TYPE slis_t_fieldcat_alv.

DATA: i_title_sl_no_track TYPE lvc_title VALUE 'SERIAL NO TRACKER'.

DATA : BEGIN OF itab OCCURS 0,

obknr LIKE objk-obknr, "OBJECT LIST

obzae LIKE objk-obzae, "OBJECT COUNTER

kunde LIKE ser01-kunde, "CUSTOMER NO

matnr LIKE objk-matnr, "PART NO

sernr LIKE objk-sernr, "SERIAL NO

mblnr LIKE ser03-mblnr, "GRN/MATERIAL DOC NO

lief_nr LIKE ser01-lief_nr,"OUTBOUND DELV NO

datum LIKE ser01-datum, "OUTBOUND DELV DATE

taser LIKE objk-taser, "HEADER TABLE

anzsn LIKE ser01-anzsn, "NO OF SERIAL NOS

vgbel LIKE lips-vgbel, "SALES ORDER NO

END OF itab.

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_kunde FOR ser01-kunde,

s_vgbel FOR lips-vgbel,

s_matnr FOR objk-matnr,

s_sernr FOR objk-sernr,

s_mblnr FOR ser03-mblnr,

s_lifnr FOR ser01-lief_nr,

s_datum FOR ser01-datum.

SELECTION-SCREEN : END OF BLOCK b1.

PERFORM tracking.

PERFORM fldcat.

PERFORM build_layout.

***CALL FUNCTION TO DISPLAY IN ALV FORMAT*******

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = sy-repid

i_grid_title = i_title_sl_no_track

is_layout = layout

it_fieldcat = p_fieldtab[]

i_save = 'A'

it_events = v_events

TABLES

t_outtab = itab.

***END OF CALL FUNCTION

FORM tracking.

SELECT objk~obknr

objk~obzae

objk~matnr

objk~sernr

objk~taser

ser01~kunde

ser01~lief_nr

ser01~datum

ser01~anzsn

ser03~mblnr

lips~vgbel

INTO CORRESPONDING FIELDS OF TABLE itab

FROM objk INNER JOIN ser01 ON objk_{obknr = ser01}obknr

INNER JOIN ser03 ON objk_{obknr = ser03}obknr

INNER JOIN lips ON ser01_{lief_nr = lips}vbeln

WHERE objk_{taser = 'SER01' OR objk}taser = 'SER03'

AND objk~matnr IN s_matnr

AND objk~sernr IN s_sernr

AND ser01~kunde IN s_kunde

AND ser01~lief_nr IN s_lifnr

AND ser01~datum IN s_datum

AND ser03~mblnr IN s_mblnr

AND lips~vgbel IN s_vgbel.

ENDFORM. "TRACKING

*&----

*& Form fldcat

*&----

FORM fldcat.

PERFORM fldcat1 USING 'KUNDE' 'Customer No'.

PERFORM fldcat1 USING 'VGBEL' 'Sales Order No'.

PERFORM fldcat1 USING 'MATNR' 'Tejas Part No'.

PERFORM fldcat1 USING 'SERNR' 'Serial No'.

PERFORM fldcat1 USING 'MBLNR' 'Material Doc No'.

PERFORM fldcat1 USING 'LIEF_NR' 'Outbound Delv No'.

PERFORM fldcat1 USING 'DATUM' 'Outbound Delv Date'.

ENDFORM. "fldcat

*&----

*& Form fldcat1

*&----

FORM fldcat1 USING fnam ftext.

DATA: l_fieldcat TYPE slis_fieldcat_alv.

l_fieldcat-fieldname = fnam.

l_fieldcat-seltext_m = ftext.

IF fnam EQ 'MATNR'."OR fnam EQ 'PKUNAG'.

l_fieldcat-key = 'X'.

ENDIF.

layout-colwidth_optimize = 'X'.

APPEND l_fieldcat TO p_fieldtab.

clear fldcat1.

ENDFORM. "fldcat1

*&----

*& Form build_layout

*&----

FORM build_layout .

layout-no_input = 'X'.

layout-colwidth_optimize = 'X'.

layout-zebra = 'X'.

layout-totals_text = 'Totals'(256).

layout-coltab_fieldname = 'CELL_COLOUR'.

*clear layout.

ENDFORM. "TRACKING

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:20:45 GMT

hi now it is not showing the error message, but the join condition is not correct the jion condition is like this,

1. a_{field = b}field

2. a_{field = c}field

b_{field = c}field

3 . a_{field = d}field

b_{field = d}field

c_{field = d}field

REPORT ZT_SERIAL.

TABLES : SER01,SER03,OBJK,LIKP,LIPS.

TYPE-POOLS: SLIS.

DATA: AFIELD TYPE SLIS_FIELDCAT_ALV.

DATA: FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV..

DATA: V_EVENTS TYPE SLIS_T_EVENT.

DATA: LAYOUT TYPE SLIS_LAYOUT_ALV.

DATA: P_FIELDTAB TYPE SLIS_T_FIELDCAT_ALV.

DATA: I_TITLE_SL_NO_TRACK TYPE LVC_TITLE VALUE 'SERIAL NO TRACKER'.

DATA : BEGIN OF ITAB OCCURS 0,

OBKNR LIKE OBJK-OBKNR, "OBJECT LIST

OBZAE LIKE OBJK-OBZAE, "OBJECT COUNTER

KUNDE LIKE SER01-KUNDE, "CUSTOMER NO

MATNR LIKE OBJK-MATNR, "PART NO

SERNR LIKE OBJK-SERNR, "SERIAL NO

MBLNR LIKE SER03-MBLNR, "GRN/MATERIAL DOC NO

LIEF_NR LIKE SER01-LIEF_NR,"OUTBOUND DELV NO

DATUM LIKE SER01-DATUM, "OUTBOUND DELV DATE

TASER LIKE OBJK-TASER, "HEADER TABLE

ANZSN LIKE SER01-ANZSN, "NO OF SERIAL NOS

VGBEL LIKE LIPS-VGBEL, "SALES ORDER NO

END OF ITAB.

SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_KUNDE FOR SER01-KUNDE,

S_VGBEL FOR LIPS-VGBEL,

S_MATNR FOR OBJK-MATNR,

S_SERNR FOR OBJK-SERNR,

S_MBLNR FOR SER03-MBLNR,

S_LIFNR FOR SER01-LIEF_NR,

S_DATUM FOR SER01-DATUM.

SELECTION-SCREEN : END OF BLOCK B1.

PERFORM TRACKING1.

PERFORM FLDCAT.

PERFORM BUILD_LAYOUT.

***CALL FUNCTION TO DISPLAY IN ALV FORMAT*******

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = SY-REPID

I_GRID_TITLE = I_TITLE_SL_NO_TRACK

IS_LAYOUT = LAYOUT

IT_FIELDCAT = P_FIELDTAB[]

I_SAVE = 'A'

IT_EVENTS = V_EVENTS

TABLES

T_OUTTAB = ITAB.

***END OF CALL FUNCTION

*&----

**& Form fldcat

*&----

*text

*----

FORM FLDCAT.

PERFORM FLDCAT1 USING 'KUNDE' 'Customer No'.

PERFORM FLDCAT1 USING 'VGBEL' 'Sales Order No'.

PERFORM FLDCAT1 USING 'MATNR' 'Tejas Part No'.

PERFORM FLDCAT1 USING 'SERNR' 'Serial No'.

PERFORM FLDCAT1 USING 'MBLNR' 'Material Doc No'.

PERFORM FLDCAT1 USING 'LIEF_NR' 'Outbound Delv No'.

PERFORM FLDCAT1 USING 'DATUM' 'Outbound Delv Date'.

ENDFORM. "fldcat

*&----

**& Form fldcat1

*&----

*text

*----

*-->FNAM text

*-->FTEXT text

*----

FORM FLDCAT1 USING FNAM FTEXT.

DATA: L_FIELDCAT TYPE SLIS_FIELDCAT_ALV.

L_FIELDCAT-FIELDNAME = FNAM.

L_FIELDCAT-SELTEXT_M = FTEXT.

IF FNAM EQ 'MATNR'."OR fnam EQ 'PKUNAG'.

L_FIELDCAT-KEY = 'X'.

ENDIF.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

APPEND L_FIELDCAT TO P_FIELDTAB.

CLEAR fieldcat.

ENDFORM. "fldcat1

*&----

**& Form build_layout

*&----

*text

*----

FORM BUILD_LAYOUT .

LAYOUT-NO_INPUT = 'X'.

LAYOUT-COLWIDTH_OPTIMIZE = 'X'.

LAYOUT-ZEBRA = 'X'.

LAYOUT-TOTALS_TEXT = 'Totals'(256).

layout-coltab_fieldname = 'CELL_COLOUR'.

*clear layout.

ENDFORM.

&----

*& Form TRACKING1

&----

text

----

--> p1 text
<-- p2 text

----

form TRACKING1 .

SELECT a~OBKNR

a~OBZAE

a~MATNR

a~SERNR

a~TASER

b~KUNDE

b~LIEF_NR

b~DATUM

b~ANZSN

c~MBLNR

d~VGBEL

INTO TABLE ITAB FROM ( ( ( OBJK as a

INNER JOIN SER01 as b ON a_{OBKNR = b}OBKNR )

INNER JOIN SER03 as c ON a_{OBKNR = c}OBKNR )

INNER JOIN LIPS as d ON b_{LIEF_NR = d}VBELN )

WHERE

a~TASER = 'SER01'

OR a~TASER = 'SER03'

and a~MATNR IN S_MATNR AND

a~SERNR IN S_SERNR AND

b~KUNDE IN S_KUNDE AND

b~LIEF_NR IN S_LIFNR AND

b~DATUM IN S_DATUM AND

c~MBLNR IN S_MBLNR AND

d~VGBEL IN S_VGBEL.

endform. " TRACKING1

regards,

venkat.

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:44:09 GMT

Hi Anju,

Your inputs are correct but when I run the report this time,I'm getting blank report as the output,even though I have data in those tables. Further inputs might help...

Thanks,

Bhavin

Re: Help on Inner Join Query

Former Member — Tue, 11 Mar 2008 12:48:08 GMT

Hi VEnkat,

When I use your code,I'm getting blank report as the output,Further inputs might help...

Thanks,

Bhavin