REGARDING SELECT STATEMENT

[Former Member]

Hi

i want to display material description form purchase order into my new z report againts g/l account

i.e g/l account -> pur order -> materialtext in po.

so i have written my select query as below

select hkont bukrs belnr gjahr shkzg dmbtr bzdat anln1 prctr sgtxt ebeln matnr from bseg

into (itab-hkont,itab-bukrs, itab-belnr, itab-gjahr, itab-shkzg, itab-dmbtr,

itab-bzdat, itab-anln1, itab-prctr, itab-sgtxt,itab-ebeln,itab-matnr)

where bukrs = 1000

and hkont in hkont

and gjahr in gjahr

and bzdat >= bzdat-low and bzdat <= bzdat-high.

SELECT TXZ01 FROM EKPO INTO ITAB-TXZ01 WHERE EBELN = ITAB-EBELN.

APPEND ITAB.

CLEAR ITAB.

endselect.

endselect.

my problem is that those g/l account account that do not have purchase is not getting display.

only g/l account with PO is getting displayed.

but i want both g/l account to be dispalyed.

i think my problem is in my second select query.

can anybody help me.

Thanks & Regards

Lalith.

[Former Member]

HI LALITH,

ALSO IF YOU WANT TO IMPROVE PERFORMANCE AVOID USING SELECT...ENDSELECT

you can join both tables...bseg and makt

select a_{hkont a}bukrs a_{belnr a}gjahr a_{shkzg a}dmbtr a_{bzdat a}anln1 a_{prctr a}sgtxt a_{ebeln a}matnr b~TXZ01

from bseg as a

inner join makt as b

on a_{matnr = b}matnr

into (itab-hkont,itab-bukrs, itab-belnr, itab-gjahr, itab-shkzg, itab-dmbtr,

itab-bzdat, itab-anln1, itab-prctr, itab-sgtxt,itab-ebeln,itab-matnr,itab-TXZ01)

where bukrs = 1000

and hkont in hkont

and gjahr in gjahr

and bzdat >= bzdat-low and bzdat <= bzdat-high.

[Former Member]

Recode like this

select hkont bukrs belnr gjahr shkzg dmbtr bzdat anln1 prctr sgtxt ebeln matnr from bseg

into (itab-hkont,itab-bukrs, itab-belnr, itab-gjahr, itab-shkzg, itab-dmbtr,

itab-bzdat, itab-anln1, itab-prctr, itab-sgtxt,itab-ebeln,itab-matnr)

where bukrs = 1000

and hkont in hkont

and gjahr in gjahr

and bzdat >= bzdat-low and bzdat <= bzdat-high.

SELECT single TXZ01 FROM EKPO INTO ITAB-TXZ01 WHERE EBELN = ITAB-EBELN.

APPEND ITAB.

CLEAR ITAB.

endselect.

rewards if useful

thanks Arjun

[Former Member]

hi

select hkont bukrs belnr gjahr shkzg dmbtr bzdat anln1 prctr sgtxt ebeln matnr from bseg

into (itab-hkont,itab-bukrs, itab-belnr, itab-gjahr, itab-shkzg, itab-dmbtr,

itab-bzdat, itab-anln1, itab-prctr, itab-sgtxt,itab-ebeln,itab-matnr)

where bukrs = 1000

and hkont in hkont

and gjahr in gjahr

and bzdat >= bzdat-low and bzdat <= bzdat-high.

APPEND ITAB.

CLEAR ITAB.

endselect.

loop at itab.

SELECT TXZ01 FROM EKPO INTO ITAB-TXZ01 WHERE EBELN = ITAB-EBELN.

modify itab index sy-tabix.

endloop.

[Former Member]

HI LALITH,

TRUE..YOUR 2ND QUERY HAS FAULT...

YOU NEED MATERIAL DESCRIPATION...THEN WHY ARE YOU CHECKING IN EKPO...

GO TO MAKT

SELECT MAKTX FROM MAKT INTO ITAB-TXZ01 WHERE MATNR = ITAB-MATNR.

REWARD POINTS IF USEFUL....

[Former Member]

Hi,

make sure that you have passed on the correct G/L account that is HKONT on to ur select query.

I think that the G/L account u are passing fetches only the Items with PO .

Check ur Selection Logic.

Regards,

Balakumar.G.

Reward Points if Helpful.

[Former Member]

HI

SELECT SINGLE DOES NOT WORK SUPPOSE THERE ARE 10 LINE ITEMS IN PO ONLY 1 METERIAL DECP IS SELECTED BUT G/L ACCOUNT WITHOUT PO WILL BE DISPLAYED.

i tried this before posting here

Thanks & Regrds

Lalith.

[Former Member]

HI LALITH,

ALSO IF YOU WANT TO IMPROVE PERFORMANCE AVOID USING SELECT...ENDSELECT

you can join both tables...bseg and makt

select a_{hkont a}bukrs a_{belnr a}gjahr a_{shkzg a}dmbtr a_{bzdat a}anln1 a_{prctr a}sgtxt a_{ebeln a}matnr b~TXZ01

from bseg as a

inner join makt as b

on a_{matnr = b}matnr

into (itab-hkont,itab-bukrs, itab-belnr, itab-gjahr, itab-shkzg, itab-dmbtr,

itab-bzdat, itab-anln1, itab-prctr, itab-sgtxt,itab-ebeln,itab-matnr,itab-TXZ01)

where bukrs = 1000

and hkont in hkont

and gjahr in gjahr

and bzdat >= bzdat-low and bzdat <= bzdat-high.

[Former Member]

Hi kumar

looping does not work it only selects last item all the above items are neglected.

Thanks & regards

lalith

[Former Member]

Hi

Do it this way.

First download all accounts:

SELECT hkont bukrs belnr gjahr shkzg dmbtr bzdat anln1 prctr sgtxt ebeln matnr from bseg 
  INTO TABLE itab
  WHERE bukrs = 1000
    AND hkont IN hkont
    AND gjahr IN gjahr
    AND bzdat >= bzdat-low 
    AND bzdat <= bzdat-high.

Then select all material descriptions

IF itab[] IS NOT INITIAL.
  SELECT ebeln yxz01
    INTO TABLE itab2
    FROM ekpo 
    FOR ALL ENTRIES IN itab
    WHERE ebeln = itab-ebeln.
ENDIF.

Then add description to the itab with accounts

LOOP AT itab ASSIGNING <itab>.
  READ TABLE itab2 INTO lwa_itab2 WITH TABLE KEY ebeln = <itab>-ebeln.
  IF sy-subrc = 0.
    <itab>-txz01 = lwa_itab2-txz01.
  ENDIF.
ENDLOOP.

Rgds

Mat