query for this cenario

[Former Member]

hi experts,

I have two table here .lips and qals.

in delivery i have a delivery order with three line items .

8000001 ->mat1 -> 10 tons ->batch no 100

8000001->mat1 -> 20 tons ->batch no 100

8000001->mat1 -> 15 tons ->batch no 100

in qals table i have

batch no 1000 -> mat1 ->lot no 10000

my query is

SELECT * FROM qals INTO TABLE it_qals FOR ALL ENTRIES IN it_lips WHERE charg EQ it_lips-charg AND matnr EQ it_lips-matnr.

here charg eq batchno and matnr eq material no

as a result i want to get the lot number.

i am getting

10000 once

but now i need to get

10000

10000

10000

bcoz there are three line items in lips table.So how i can change my query?

regards ,

mani

[Former Member]

Hi,

Instead of doing for all entries, can you do this?

LOOP AT IT_LIPS

SELECT * FROM qals

APPENDING TABLE it_qals

WHERE charg EQ it_lips-charg AND

matnr EQ it_lips-matnr.

ENDLOOP.

This way you will get all records.

ashish

[former_member189629]

Mani,

Did u c if QALS has multiple lines for your parameters (I mean does it have 3 lines for the same batch & matnr)? If it does, try removing the batch from the WHERE condition as FOR ALL ENTRIES addition will fetch all lines matching the WHERE condition..

I suggest u use FOR ALL ENTRIES instead of nested SELECTs

[Former Member]

no karthik it has only one entry for that condition thats my problem

[former_member189629]

Mani,

If QALS has only 1 entry, after the SELECT, loop thru LIPS and APPEND values of QALS into a final table, in the format u want, which u will output.