topic Re: Where condition in Application Development and Automation Discussions

Where condition

Former Member — Wed, 29 Oct 2008 11:31:04 GMT

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.

Re: Where condition

christine_evans — Wed, 29 Oct 2008 11:33:34 GMT


> 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.

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.

Re: Where condition

Former Member — Wed, 29 Oct 2008 11:34:15 GMT

Hi,

Sorry wrongly posted

Thanks & Regards,

Navneeth K.

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

Re: Where condition

Former Member — Wed, 29 Oct 2008 11:34:45 GMT

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.

Re: Where condition

Former Member — Wed, 29 Oct 2008 11:36:39 GMT

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

Re: Where condition

Former Member — Wed, 29 Oct 2008 11:37:30 GMT

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

Re: Where condition

christine_evans — Wed, 29 Oct 2008 11:49:52 GMT

> >


> > 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.

Re: Where condition

Former Member — Wed, 29 Oct 2008 11:53:40 GMT

Hi,

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

Re: Where condition

Former Member — Wed, 29 Oct 2008 12:21:09 GMT

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

Re: Where condition

Former Member — Wed, 29 Oct 2008 12:38:33 GMT

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.

Re: Where condition

Former Member — Wed, 29 Oct 2008 12:39:12 GMT

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.

Re: Where condition

Former Member — Wed, 29 Oct 2008 14:01:36 GMT

Hi Jean,

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

Did you try the code I pasted ?

regards,

Advait

Re: Where condition

Former Member — Wed, 29 Oct 2008 14:09:07 GMT

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,

Re: Where condition

Former Member — Thu, 30 Oct 2008 04:03:30 GMT

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

Thanx anyways....

Re: Where condition

Former Member — Thu, 30 Oct 2008 05:28:15 GMT

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?

Re: Where condition

Former Member — Thu, 30 Oct 2008 21:10:09 GMT

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

Re: Where condition

Former Member — Fri, 31 Oct 2008 03:57:06 GMT

Thank u so much ....