Inner Join problem

[Former Member]

Hello guys,

I have a problem in my select query. My query is turning empty. there isnt problem before without adding vbak table.Where is  problem?

select  matnr fkdat matkl lfimg v~auart from lips

            inner join likp

            on LIKP~VBELN = LIPS~VBELN

            join vbak as v

            on likp~vbeln = v~vbeln

       into CORRESPONDING FIELDS OF TABLE itab_material

       where  LIPS~LFIMG ne 0 and likp~VKORG eq 'O001' and likp~WADAT_IST eq itab_plaka-fkdat.

Thank you.,

Regards,

B.Y.

[Former Member]

Hi Bekir,

In LIPS table, the 'VBELN' it`s not sales order number, it`s delivery order number,

you need find out the sales order number from 'VBFA' by delivery order number.

so your query should like this:

   select  lips~matnr fkdat matkl lfimg v~auart from lips

            inner join likp

            on LIKP~VBELN = LIPS~VBELN

            inner join vbfa

            on vbfa~vbeln = LIPS~VBELN

            inner join vbak as v

            on v~vbeln = vbfa~vbelv      

       into CORRESPONDING FIELDS OF TABLE itab_material

       where  LIPS~LFIMG ne 0 and likp~VKORG eq 'O001' and likp~WADAT_IST eq itab_plaka-fkdat.

regards,

Archer

[SimoneMilesi]

the error is here

on likp~vbeln = v~vbeln

Likp-vbeln contains Delivery nr

VBAK-Vbeln contains Order nr!

if a select doesn't works, try to simulate it navigating in SE16!

[former_member201275]

    SELECT a~matnr b~fkdat a~matkl a~lfimg v~auart
    INTO CORRESPONDING FIELDS OF TABLE itab_material
    FROM ( ( lips as a
    INNER JOIN likp as b
    ON a~vbeln = b~vbeln )
    INNER JOIN vbak AS v
    ON a~vbeln = v~vbeln )
    WHERE a~lfimg NE 0
    AND b~vkorg EQ 'O001'
    AND b~wadat_ist EQ itab_plaka-fkdat.

[Sayan_C]

Hi,

As your source code If you want to select a relative of SO->DO-INV or INV->DO-SO.

Please use table VBFA for the relation.

Hope it help.

[Former Member]

Hi,

     Table Lips is having field VGBEL which stores SO No(Originating Doc) and in Query their is a connection lips-vbeln = vbak-vbeln , which is wrong i,e DELIVERY NO = SO No,

     Also Check for the value of itab_plaka-fkdat

Replace Like

select  matnr fkdat matkl lfimg v~auart from lips

            inner join likp

            on LIKP~VBELN = LIPS~VBELN

            join vbak as v

            on lips~vgbel = v~vbeln

       into CORRESPONDING FIELDS OF TABLE itab_material

       where  LIPS~LFIMG ne 0 and likp~VKORG eq 'O001' and likp~WADAT_IST eq itab_plaka-fkdat.

Regards

[RaymondGiuseppi]

Hi,

You cannot join delivery and sales order expecting them to share same number, I would suggest you first read some online document and this reference note :  185530 - Performance: Customer developments in SD where you will find many examples.

Actually delivery carry the SO number (preceding document, field LIPS-VGBEL) so you could join VBAK easily. But if you actually intend to add selection criteria from SO information, you may be required to also use table vbfa for performance. (look in the note for "Search for deliveries with sales order number")

Regards,

Raymond