topic Re: Inner Join problem in Application Development and Automation Discussions

Inner Join problem

Former Member — Thu, 04 Sep 2014 08:23:03 GMT

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.

Re: Inner Join problem

Former Member — Thu, 04 Sep 2014 08:52:43 GMT

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

Re: Inner Join problem

SimoneMilesi — Thu, 04 Sep 2014 08:59:08 GMT

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!

Re: Inner Join problem

former_member201275 — Thu, 04 Sep 2014 09:01:33 GMT

    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.

Re: Inner Join problem

Sayan_C — Thu, 04 Sep 2014 09:05:28 GMT

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.

Re: Inner Join problem

Former Member — Thu, 04 Sep 2014 09:26:19 GMT

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

Re: Inner Join problem

RaymondGiuseppi — Thu, 04 Sep 2014 10:14:43 GMT

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