topic Re: query for this cenario in Application Development and Automation Discussions

query for this cenario

Former Member — Tue, 25 Sep 2007 04:59:12 GMT

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

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

regards ,

mani

Re: query for this cenario

Former Member — Tue, 25 Sep 2007 05:14:23 GMT

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

Re: query for this cenario

former_member189629 — Tue, 25 Sep 2007 05:14:24 GMT

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

Re: query for this cenario

Former Member — Tue, 25 Sep 2007 05:17:33 GMT

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

Re: query for this cenario

former_member189629 — Tue, 25 Sep 2007 05:41:03 GMT

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.