simple select statements

[Former Member]

hello experts,

can anyone guide me writing the select statements,

here r the output fields ( ekko-ebeln, ekpo-ebelp, ekpo-matnr, ekko-lifnr, lfa1-name1, ekpo-werks, eket-eindt, ekpo-menge, ekpo-meins, ekpo-netpr, ekpo-kepnr, ekpo-netpr, eine-infnr, eine-netpr)

if the user enters the purchasing group(ekko-ekgrp) then how to wirte the select statement or

if the user enter the material group( ekpo-matkl) then how to write the select statement...

plz some one help me...

thanks a lot for your valuable time

SIRI

[RichHeilman]

Use SELECT-OPTIONS in your selection screen, then if the user enters either or, you only have to write one SELECT statement, in the WHERE clause add this ....

.....
WHERE ekko~ekgrp in s_ekgrp
     and   ekpo~matkl in s_maktl.
.....

I assume that you know that you must do an inner join for this SELECT statement.

Regards,

Rich HEilman

[Former Member]

Rich

ya i can design the selection screen with select option and i can write the select statements to retrieve the data

so my question is...

here the user enter the data in purchasing group field, it is coming from ekko table rite then how link this with other tables ekpo, eine, eket and lfa1....

shall i have to retrieve the data from ekko and then for all entries in that internal table i have to retrieve the data from other table or only one select statement with inner joins is sufficient to get the data.....

where as the second case with material group it is coming from ekpo table, i think its better to write the single select statement with inner joins in this case

can u guide me the logic for the query....

SIRI

[RichHeilman]

Well, you can try it with one SELECT statement and see performance, if it is good, then leave it alone, if bad, then you may have to go another way. As long as you are using the keys to join, I would think it will be pretty fast.

Regards,

Rich Heilman

[Former Member]

You mean to say that...

In both the conditions ie if the user enters either purchasing group or material group only one select statement is enough rite.....

one more question rich.. once i get the records i have to take only the open purchase records.. condition applies here is EKPO-ELIKZ = ' '.

where exactly i have to apply this condition.. once i get all the records or before that only i have to put this condition..

can guide me plz

SIRI

[Former Member]

There is an index which contains ELIKZ field in EKPO. So you can use it in query itself.

Thanks,

SKJ

[Former Member]

Hi,

Try this..

TABLES: ekko, ekpo.

SELECT-OPTIONS: so_ekgrp FOR ekko-ekgrp.

SELECT-OPTIONS: so_matkl FOR ekpo-matkl.

DATA: BEGIN OF wa_po_detail,

ebeln TYPE ekko-ebeln,

ebelp TYPE ekpo-ebelp,

matnr TYPE ekpo-matnr,

lifnr TYPE ekko-lifnr,

name1 TYPE lfa1-name1,

werks TYPE ekpo-werks,

eindt TYPE eket-eindt,

menge TYPE ekpo-menge,

meins TYPE ekpo-meins,

netpr TYPE ekpo-netpr,

kepnr TYPE ekpo-kepnr,

infnr TYPE eine-infnr,

pi_netpr TYPE eine-netpr,

END OF wa_po_details.

DATA: lt_po_details LIKE TABLE OF wa_po_details.

• Get the details.

SELECT a_{ebeln b}ebelp b~matnr

a_{lifnr d}name1 b~werks

c_{eindt b}menge b~meins

b_{netpr b}kepnr

INTO TABLE lt_po_details

FROM ekko AS a INNER JOIN ekpo AS b

ON a_{ebeln = b}ebeln

INNER JOIN eket AS c

ON b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp

INNER JOIN lfa1 AS d

ON a_{lifnr = b}lifnr

WHERE a~ekgrp IN so_ekgrp

AND b~matkl IN so_matkl.

Thanks,

Naren