Where condition

[Former Member]

Hi people,

I have used an inner-join between two tables ekko and ekpo as shown below:

SELECT ekpo~ebeln

ekpo~ebelp

ekpo~zdat01

ekko~lifnr

FROM ekpo

INNER JOIN ekko

ON ekpo_{ebeln = ekko}ebeln

INTO TABLE gt_ekpo_ekko

FOR ALL ENTRIES IN gt_vbfa

WHERE ekpo~ebeln = gt_vbfa-vbeln

and ekpo~ebelp = gt_vbfa-posnn.

But since EBELP and GT_VBFA-POSNN do not have the same length, i will not be able to proceed with the selection. Please tell me how to overcome this problem.

[christine_evans]

>

> Hi people,
> 
> I have used an inner-join between two tables ekko and ekpo as shown below:
> SELECT ekpo~ebeln
>            ekpo~ebelp
>            ekpo~zdat01
>            ekko~lifnr
>       FROM ekpo
>      INNER JOIN ekko
>         ON ekpoebeln = ekkoebeln
>       INTO TABLE gt_ekpo_ekko
>        FOR ALL ENTRIES IN gt_vbfa
>      WHERE ekpo~ebeln = gt_vbfa-vbeln
>      and ekpo~ebelp = gt_vbfa-posnn.
> 
> 
> But since EBELP and GT_VBFA-POSNN do not have the same length, i will not be able to proceed with the selection. Please tell me how to overcome this problem.

It's a SAP inconsistency and the limitations of open SQL won't let you get around it. You can do it in real SQL but the easiest thing to do is to split it into two selects using FOR ALL ENTRIES.

[Former Member]

Hi,

Sorry wrongly posted

Thanks & Regards,

Navneeth K.

Edited by: Navneeth Bothra on Oct 29, 2008 12:36 PM

[Former Member]

Hi Jean,

You can declare a Local variable of type ebelp.

Then you can assign the value to this local variable.

You can use this local variable in your sele t query.

As you are using For all entries, you can form a RANGE for EBELP, with the required length. Fill the Range with all the values from the table. Use the range in your selection instead of table field directly.

Hope this is helpful.

Best Regards,

Ram.

[Former Member]

Hi,

Can u please write it in terms of code how exactly u create and use this range.

[Former Member]

Hi,

loop at gt_vbfa into gwa_vbfa.

gwa_ebelp-sign = gc_i.

gwa_ebelp-option = gc_eq.

gwa_ebelp-low = gwa_vbfa-posnn.

APPEND gwa_ebelp TO gr_ebelp.

CLEAR gwa_ebelp-low.

endloop.

IF NOT gt_vbfa IS INITIAL.

SELECT ekpo~ebeln

ekpo~ebelp

ekpo~zdat01

ekko~lifnr

FROM ekpo

INNER JOIN ekko

ON ekpo_{ebeln = ekko}ebeln

INTO TABLE gt_ekpo_ekko

FOR ALL ENTRIES IN gt_vbfa

WHERE ekpo~ebeln = gt_vbfa-vbeln

and ekpo~ebelp in gr_ebelp.

Will this help? It is not giving me any syntax error though.

[Former Member]

Hi,

loop at gt_vbfa into gwa_vbfa.

gwa_ebelp-sign = gc_i.

gwa_ebelp-option = gc_eq.

gwa_ebelp-low = gwa_vbfa-posnn.

APPEND gwa_ebelp TO gr_ebelp.

CLEAR gwa_ebelp-low.

endloop.

IF NOT gt_vbfa IS INITIAL.

SELECT ekpo~ebeln

ekpo~ebelp

ekpo~zdat01

ekko~lifnr

FROM ekpo

INNER JOIN ekko

ON ekpo_{ebeln = ekko}ebeln

INTO TABLE gt_ekpo_ekko

FOR ALL ENTRIES IN gt_vbfa

WHERE ekpo~ebeln = gt_vbfa-vbeln

and ekpo~ebelp in gr_ebelp.

Will this help? It is not giving me any syntax error though.

[Former Member]

Hi Jean,

No your code wont solve the problem. The query would run slower instead.

Did you try the code I pasted ?

regards,

Advait

[Former Member]

No I have not yet tried your code.... I will surely try it out....

Thanx anyways....

[Former Member]

hi,

take one more field in gt_vbfa with dataelement of ebelp. and move data from gt_vbfa-posnn to new field..

then in the select stmt use where condition based on new field..

Hope it helps..

Rgds.,

subash

[Former Member]

try this..

while declaring in the internal table gt_ekpo_ekko

give ebelp as vbfa-posnn and then try the same above ur code and reply.

regards,

padma

[christine_evans]

>

> >

> > But since EBELP and GT_VBFA-POSNN do not have the same length, i will not be able to proceed with the selection. Please tell me how to overcome this problem.

> It's a SAP inconsistency and the limitations of open SQL won't let you get around it. You can do it in real SQL but the easiest thing to do is to split it into two selects using FOR ALL ENTRIES.

Sorry, I didn't read properly and answered as if you were including both fields in a join, which you can't do. Creating a new field in the itab with the right length and copying the value to it as suggested above should fix it.

[Former Member]

Hi,

What you can do is build a key table from the gt_vbfa-vbeln and gt_vbfa-posnn, say gt_po_key , this table should have only 2 fields ebeln and ebelp.

Soemthing like this.

loop at gt_vbfa assigning <fs>.
move <fs>-vbeln to gt_po_key-ebeln.
move <fs>-posnn to gt_po_key-ebelp.
append gt_po_key.
endloop.
Now use that table in the for all entries.

SELECT ekpo~ebeln
ekpo~ebelp
ekpo~zdat01
ekko~lifnr
FROM ekpo
INNER JOIN ekko
ON ekpo~ebeln = ekko~ebeln
INTO TABLE gt_ekpo_ekko
FOR ALL ENTRIES IN gt_po_key
WHERE ekpo~ebeln = gt_po_key-ebeln
and ekpo~ebelp = gt_po_key-ebelp.

Hope this helps.

regards,

Advait

regards,

Advait

[Former Member]

Is this not equivalent to using work area instead of field-symbols?

What is advantage of using field-symbols here when it is doing a work similar to what a work area does?

[Former Member]

Hi,

Field symbols are faster than work area. In work area there is a overhead of copying the itab contents to the work area and when you change some data and use the modify statement , it has to copy the contents back to the internal table. But the field symbol only points to the current row of the internal table and when you change the component of the field symbol it directly gets updated in the internal table. So you save 2 extra operations with field symbols.

regards,

Advait

[Former Member]

Thank u so much ....

[Former Member]

just declare in your internal table gt_vbfa:

posnn like ekpo-ebelp.

and use any conversion routine like conversion_exit_alpha_input .

hope it helps,