topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Mon, 11 Jun 2007 07:47:22 GMT

hi all,

can anyone please help me out on correcting this query, i am nt getting the value for lgobe,

SELECT MSEG~MBLNR

MSEG~ERFME

MSEG~ERFMG

MSEG~LGORT

MSEG~BWART

MSEG~LIFNR

MAKT~MATNR

MAKT~MAKTX

LFA1~NAME1 INTO CORRESPONDING FIELDS OF TABLE IT_HEADER

FROM ( MSEG INNER JOIN MAKT ON MSEG_{MATNR = MAKT}MATNR )

INNER JOIN LFA1 ON MSEG_{LIFNR = LFA1}LIFNR

WHERE MSEG~MBLNR = MBLNR.

IF NOT ( IT_HEADER-LGORT EQ SPACE ).
SELECT SINGLE LGOBE INTO IT_HEADER FROM T001L
WHERE LGORT = IT_HEADER-LGORT.
ENDIF.

Re: select query

former_member784222 — Mon, 11 Jun 2007 07:52:42 GMT

Hi,

I observe from the query that you are populating internal table IT_HEADER from MSEG and T001L. The query would have populated the internal table. The header line in the internal table will be empty at end of this query.

So IT_HEADER-LGORT will be space.

So you need to use:

loop at it_header.

SELECT SINGLE LGOBE INTO IT_HEADER FROM T001L

WHERE LGORT = IT_HEADER-LGORT.

endloop.

Thanks and regards,

S. Chandramouli.

Re: select query

Former Member — Mon, 11 Jun 2007 07:58:53 GMT

Hi,

When you write corresponding fields of table, it takes all the possible records from the two tables in the join statement in one shot.

For that it does not use the workarea of your internal table and thus the values in the workarea of the internal table are all blank or initial.

You would have to loop at your internal table to get values in IT_HEADER-LGORT.

Regards,

Suruchi

Re: select query

Former Member — Mon, 11 Jun 2007 10:10:40 GMT

HI,

i have tried as u said but its not working out still. you meant like this rite?

FORM GET_HEADER .

SELECT MSEG~MBLNR

MSEG~ERFME

MSEG~ERFMG

MSEG~LGORT

MSEG~BWART

MSEG~LIFNR

MAKT~MATNR

MAKT~MAKTX

LFA1~NAME1 INTO CORRESPONDING FIELDS OF TABLE IT_HEADER

FROM ( MSEG INNER JOIN MAKT ON MSEG_{MATNR = MAKT}MATNR )

INNER JOIN LFA1 ON MSEG_{LIFNR = LFA1}LIFNR

WHERE MSEG~MBLNR = P_MBLNR.

LOOP AT IT_HEADER.

IF NOT ( IT_HEADER-LGORT EQ SPACE ).

SELECT SINGLE LGOBE INTO IT_HEADER FROM T001L

WHERE LGORT = MSEG-LGORT.

ENDIF.

ENDLOOP.

ENDFORM.

Re: select query

Former Member — Mon, 11 Jun 2007 11:03:25 GMT

Hello Abinash,

Checkout this code...

REPORT zssr_query .

TABLES: mseg, makt, lfa1.

DATA: BEGIN OF it_header OCCURS 0,

erfme LIKE mseg-erfme,

erfmg LIKE mseg-erfmg,

lgort LIKE mseg-lgort,

lifnr LIKE mseg-lifnr,

maktx LIKE makt-maktx,

name1 LIKE lfa1-name1,

lgort like t001l-lgort,

END OF it_header.

PARAMETERS: p_mblnr LIKE mseg-mblnr.

SELECT mseg_{erfme mseg}erfmg mseg~lgort

mseg_{lifnr makt}maktx lfa1~name1

INTO CORRESPONDING FIELDS OF TABLE it_header

FROM ( mseg

INNER JOIN makt

ON mseg_{matnr = makt}matnr

INNER JOIN lfa1

ON mseg_{lifnr = lfa1}lifnr )

WHERE mseg~mblnr = p_mblnr.

LOOP AT it_header.

IF NOT ( it_header-lgort EQ space ).

SELECT SINGLE lgobe INTO it_header FROM t001l

WHERE lgort = mseg-lgort.

MODIFY it_header." transporting it_header-lgobe.

ENDIF.

ENDLOOP.

<b>REWARD THE POINTS IF IT IS HELPFUL</b>

Re: select query

former_member784222 — Mon, 11 Jun 2007 11:57:29 GMT

Hi,

I should be like this:

loop at it_header.

SELECT SINGLE LGOBE INTO IT_HEADER FROM T001L

WHERE LGORT = <b>IT_HEADER-LGORT</b>. "mseg-lgort

modify it_header.

endloop.

Thanks and regards,

S. Chandra Mouli.

Message was edited by:

Chandramouli Subburathinam

Re: select query

Former Member — Mon, 11 Jun 2007 11:59:57 GMT

HI sasidhar,

i dont know what is happening where exactly is the problem, i am onto implementation, i just dnt understand is anyproblem with my code or it is with the data present i have also checked the data .i have worked exactly as u said but still m nt getting the storage location. i will even send you the complete code if u want.thanks neways men if you find any way out please let me know .i will send you complete code with this . and please if you could also help me out with selection problem i have in the same report at line-selection. i am not able to use the function module.thanks anyways man.

abinash verma

&----

*& Report ZMM_GPASS1

&----

REPORT ZMM_GPASS1.

TABLES:MKPF,

MSEG,

MAKT,

T001L,

LFA1.

DATA:BEGIN OF IT_HEADER OCCURS 0,

MBLNR LIKE MSEG-MBLNR, "Number of Material Document

ERFME LIKE MSEG-ERFME, "Unit of Entry

ERFMG LIKE MSEG-ERFMG, "Quantity in Unit of Entry

LGORT LIKE MSEG-LGORT, "Storage Location

BWART LIKE MSEG-BWART, "Movement Type (Inventory Management)

LIFNR LIKE MSEG-LIFNR, "Vendor Account Number

MATNR LIKE MAKT-MATNR, "Material Number

MAKTX LIKE MAKT-MAKTX, "Material Description (Short Text)

NAME1 LIKE LFA1-NAME1, "Name Of the Vendor

LGOBE LIKE T001L-LGOBE,

END OF IT_HEADER.

DATA:BEGIN OF IT_ITEM OCCURS 0,

BLDAT LIKE MKPF-BLDAT, "Document Date in Document

BKTXT LIKE MKPF-BKTXT, "Document Header Text

FRBNR LIKE MKPF-FRBNR, "Number of Bill of Lading at Time of Goods Receipt

MBLNR LIKE MKPF-MBLNR, "Number of Material Document

END OF IT_ITEM.

DATA:BEGIN OF IT_FINAL OCCURS 0,

V_MBLNR LIKE MSEG-MBLNR, "Number of Material Document

V_ERFME LIKE MSEG-ERFME, "Unit of Entry

V_ERFMG LIKE MSEG-ERFMG, "Quantity in Unit of Entry

V_LGORT LIKE MSEG-LGORT, "Storage Location

V_BWART LIKE MSEG-BWART, "Movement Type (Inventory Management)

V_LIFNR LIKE MSEG-LIFNR, "Vendor Account Number

V_MATNR LIKE MAKT-MATNR, "Material Number

V_MAKTX LIKE MAKT-MAKTX, "Material Description (Short Text)

V_NAME1 LIKE LFA1-NAME1, "Name Of the Vendor

V_BLDAT LIKE MKPF-BLDAT, "Document Date in Document

V_BKTXT LIKE MKPF-BKTXT, "Document Header Text

V_FRBNR LIKE MKPF-FRBNR, "Number of Bill of Lading at Time of Goods Receipt

V_LGOB(30) TYPE C, "Description of Storage Location

NUM(4),

V_LGOBE LIKE T001L-LGOBE,

END OF IT_FINAL.

DATA: CNT TYPE SY-DBCNT.

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

PARAMETERS: P_MBLNR LIKE MSEG-MBLNR ."OBLIGATORY, "Customer Number

SELECTION-SCREEN:SKIP 2.

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

PARAMETERS: RAD1 RADIOBUTTON GROUP RAD DEFAULT 'X',

RAD2 RADIOBUTTON GROUP RAD.

SELECTION-SCREEN END OF BLOCK B2.

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_MBLNR.

DATA: BEGIN OF I_MBLNR OCCURS 0,

V_MBLNR LIKE MSEG-MBLNR,

V_BWART LIKE MSEG-BWART,

END OF I_MBLNR.

IF RAD1 = 'X'.

SELECT MBLNR FROM MSEG INTO TABLE I_MBLNR

WHERE BWART = '541'.

ELSEIF RAD2 = 'X'.

SELECT MBLNR FROM MSEG INTO TABLE I_MBLNR

WHERE BWART = '551'.

ENDIF.

CALL FUNCTION 'F4IF_INT_TABLE_VALUE_REQUEST'
EXPORTING
DDIC_STRUCTURE = 'MSEG-MBLNR'
RETFIELD = I_MBLNR-V_MBLNR

PVALKEY = ' '
DYNPPROG = ' '
DYNPNR = ' '
DYNPROFIELD = ' '
STEPL = 0
WINDOW_TITLE =
VALUE = ' '
VALUE_ORG = 'C'
MULTIPLE_CHOICE = ' '
DISPLAY = ' '
CALLBACK_PROGRAM = ' '
CALLBACK_FORM = ' '
MARK_TAB =
IMPORTING
USER_RESET =

TABLES
VALUE_TAB = I_MBLNR.

FIELD_TAB =
RETURN_TAB =
DYNPFLD_MAPPING =
EXCEPTIONS
PARAMETER_ERROR = 1
NO_VALUES_FOUND = 2
OTHERS = 3

.
IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

START-OF-SELECTION.

PERFORM GET_HEADER.

IF NOT IT_HEADER[] IS INITIAL.

PERFORM GET_ITEM.

ELSE.

MESSAGE E003(ZMSG).

ENDIF.

END-OF-SELECTION.

PERFORM GET_FINAL_DATA.

PERFORM DISPLAY_DATA.

&----

*& Form GET_HEADER

&----

text

----

--> p1 text
<-- p2 text

----

FORM GET_HEADER .

SELECT MSEG~MBLNR

MSEG~ERFME

MSEG~ERFMG

MSEG~LGORT

MSEG~BWART

MSEG~LIFNR

MAKT~MATNR

MAKT~MAKTX

LFA1~NAME1 INTO CORRESPONDING FIELDS OF TABLE IT_HEADER

FROM ( MSEG INNER JOIN MAKT ON MSEG_{MATNR = MAKT}MATNR )

INNER JOIN LFA1 ON MSEG_{LIFNR = LFA1}LIFNR

WHERE MSEG~MBLNR = P_MBLNR.

LOOP AT IT_HEADER.

SELECT SINGLE LGOBE INTO IT_HEADER FROM T001L

WHERE LGORT = MSEG-LGORT.

MODIFY IT_HEADER." transporting it_header-lgobe.

ENDLOOP.

ENDFORM. " GET_HEADER

&----

*& Form GET_ITEM

&----

text

----

--> p1 text
<-- p2 text

----

FORM GET_ITEM .

SELECT BLDAT

BKTXT

FRBNR

MBLNR INTO TABLE IT_ITEM FROM MKPF

FOR ALL ENTRIES IN IT_HEADER

WHERE MBLNR = IT_HEADER-MBLNR.

ENDFORM. " GET_ITEM

&----

*& Form GET_FINAL_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM GET_FINAL_DATA .

LOOP AT IT_HEADER." WHERE LGORT NE SPACE.

LOOP AT IT_ITEM WHERE MBLNR = IT_HEADER-MBLNR.

IT_FINAL-V_MBLNR = IT_HEADER-MBLNR.

IT_FINAL-V_ERFME = IT_HEADER-ERFME.

IT_FINAL-V_ERFMG = IT_HEADER-ERFMG.

IT_FINAL-V_LGORT = IT_HEADER-LGORT.

IT_FINAL-V_BWART = IT_HEADER-BWART.

IT_FINAL-V_LIFNR = IT_HEADER-LIFNR.

IT_FINAL-V_MATNR = IT_HEADER-MATNR.

IT_FINAL-V_MAKTX = IT_HEADER-MAKTX.

IT_FINAL-V_NAME1 = IT_HEADER-NAME1.

IT_FINAL-V_LGOBE = IT_HEADER-LGOBE.

IT_FINAL-V_BLDAT = IT_ITEM-BLDAT.

IT_FINAL-V_BKTXT = IT_ITEM-BKTXT.

IT_FINAL-V_FRBNR = IT_ITEM-FRBNR.

APPEND IT_FINAL.

CLEAR IT_FINAL.

ENDLOOP.

ENDFORM. " GET_FINAL_DATA

&----

*& Form DISPLAY_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM DISPLAY_DATA .

LOOP AT IT_FINAL.
WRITE:/ IT_FINAL-V_MBLNR,
IT_FINAL-V_ERFME,
IT_FINAL-V_ERFMG,
IT_FINAL-V_LGORT,
IT_FINAL-V_BWART,
IT_FINAL-V_LIFNR,
IT_FINAL-V_MATNR,
IT_FINAL-V_MAKTX,
IT_FINAL-V_NAME1,
IT_FINAL-V_BLDAT,
IT_FINAL-V_BKTXT,
IT_FINAL-V_LGOBE,
IT_FINAL-V_FRBNR.
ENDLOOP.

IF RAD1 = 'X' .

IT_FINAL-V_BWART = '541' .

CALL FUNCTION 'OPEN_FORM'

EXPORTING

FORM = 'ZMM_GPASS1'

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'RETURN'

WINDOW = 'HEADER'

IF SY-SUBRC <> 0.

*MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

PERFORM LAYOUT.

ELSEIF RAD2 = 'X'.

IT_FINAL-V_BWART = '101' .

IF SY-SUBRC NE 0.

CALL FUNCTION 'OPEN_FORM'

EXPORTING

FORM = 'ZMM_GPASS1'

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'NONRETURN'

WINDOW = 'HEADER'

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

PERFORM LAYOUT.

ELSE.

MESSAGE I004(ZMSG).

ENDIF.

ENDFORM. " DISPLAY_DATA

&----

*& Form LAYOUT

&----

text

----

--> p1 text
<-- p2 text

----

FORM LAYOUT .

CNT = SY-DBCNT.

LOOP AT IT_FINAL.

IT_FINAL-NUM = CNT.

CALL FUNCTION 'WRITE_FORM'

EXPORTING

ELEMENT = 'ITEM_DISPLAY'

FUNCTION = 'SET'
TYPE = 'BODY'

WINDOW = 'MAIN'

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

CNT = CNT + 1.

ENDLOOP.

CALL FUNCTION 'CLOSE_FORM'

IMPORTING
RESULT =
RDI_RESULT =
TABLES
OTFDATA =
EXCEPTIONS
UNOPENED = 1
BAD_PAGEFORMAT_FOR_PRINT = 2
SEND_ERROR = 3
SPOOL_ERROR = 4
CODEPAGE = 5
OTHERS = 6

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " LAYOUT

Re: select query

Former Member — Mon, 11 Jun 2007 12:10:33 GMT

When you do the inner join in the select query, the final internal table will be populated in a single shot curcumventing the use of header line. So your header line would be empty in all the cases. Thus the 'if' condition is never executed.

In order to make code for efficent u can rewrite the second query with the addition of a for all entries.

PS reward with points if found useful

Re: select query

Former Member — Tue, 12 Jun 2007 08:20:08 GMT

ya i am able to resolve it with ur help