topic Re: Problem with the select statement in Application Development and Automation Discussions

Problem with the select statement

Former Member — Thu, 22 Mar 2007 20:58:43 GMT

hello experts,

I am retrieving the data from different table based upon some conditions... here i am giving my problem

SELECT ebeln aedat ekgrp ekorg lifnr

INTO CORRESPONDING FIELDS OF TABLE IT_EKKO_HDR FROM ekko

WHERE lifnr IN p_lifnr

AND ebeln IN p_ebeln

AND ekgrp IN p_ekgrp

AND aedat IN p_aedat

AND ernam IN p_ernam.

SELECT ekpo_{ebeln ekpo}ebelp ekpo_{menge ekpo}meins ekpo_{matnr ekpo}txz01 ekpo_{idnlf MARD}WERKS mard_{lgort mard}lgpbe t001l_{lgobe ekes}menge eket~wemng

INTO TABLE it_ekko_itm FROM ekpo

INNER JOIN eket ON ekpo_{ebeln = eket}ebeln AND ekpo_{ebelp = eket}ebelp

INNER JOIN t001l ON ekpo_{werks = t001l}werks

INNER JOIN mard ON ekpo_{matnr = mard}matnr AND ekpo_{werks = mard}werks

INNER JOIN ekes ON ekpo_{ebeln = ekes}ebeln AND ekpo_{ebelp = ekes}ebelp

FOR ALL entries IN it_ekko_hdr WHERE ekpo~ebeln = it_ekko_hdr-ebeln

AND ekpo~matnr IN p_matnr.

AND eket~eindt IN p_eindt.

LOOP AT IT_EKKO_itm.

MOVE-CORRESPONDING IT_EKKO_ITM TO IT_EKKO.

READ TABLE IT_EKKO_HDR WITH KEY EBELN = IT_EKKO-EBELN.

IF SY-SUBRC = 0.

IT_EKKO-aedat = IT_EKKO_HDR-AEDAT.

IT_EKKO-ekgrp = IT_EKKO_HDR-EKGRP.

IT_EKKO-ekorg = IT_EKKO_HDR-EKORG.

IT_EKKO-lifnr = IT_EKKO_HDR-LIFNR.

ENDIF.

APPEND IT_EKKO.

CLEAR: IT_EKKO.

ENDLOOP.

LOOP AT it_ekko INTO wa_ekko.

CLEAR wa_ekko-QUANTITY.

MODIFY it_ekko FROM wa_ekko INDEX sy-tabix.

ld_color = ld_color + 1.
if ld_color = 8.
ld_color = 1.
endif.
concatenate 'C' ld_color '10' into wa_ekko-line_color.

modify it_ekko from wa_ekko.

ENDLOOP.

Here for the field T001L-LGOBE there r different descriptions for the each description i am getting multiple line items with duplicate records

so my problem is to elimate those duplicate records, I should take only primary bin value from that field

how to modify my code, can any one guide me its bit urgent...

~~SIRI

Re: Problem with the select statement

amit_khare — Thu, 22 Mar 2007 21:01:34 GMT

Sort the internal table on the duplicate record field and use DELETE DULICATE.

Regards,

Amit

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:08:40 GMT

Move your T001L join after MARD join and change the join condition to werks = mard_{werks and lgort = mard}lgort.

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:08:44 GMT

Amit

based upon the above code can u send me modified code plz...

SIRI

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:11:11 GMT

Instead of changing the query, you do this way:

Once you get the rows in your internal table, SORT the internal table any do the DELETE ADJACENT DUPLICATES COMPARING <fields>

Thanks,

Santosh

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:19:41 GMT

Srinivas

your idea working out but one more correction i have to do in the same code

when i am applying u r logic but for the field storage location mard-lgort.. I am getting the values 0001 and 0004 i should only get the values with 0001

how to eliminate the remaining records

thax alot for ur anticipation

SIRI

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:21:44 GMT

SKJ

can u gimme the logic based upon line item number plz

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:23:44 GMT

You cannot do that as one plant can have more than one storage location. Even though there is a storage location on EKPO, it may not be filled in all cases. So you cannot really use that to select just one. If you know you have to select always one record from MARD even if there are more than one storage locations in there, you will have to add some condition to specify which of the multiple storage locations you need. Check with your functional team regarding that.

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:40:48 GMT

Ya Srini,

For one line item I should get only one storage location...like now i am getting 0001 0002 0003 and 0004 for a single line item

but i should get only line item with 0001 how to give the condition

can u gimme some guidance how to put the condition..

SIRI

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 21:58:35 GMT

You cannot do that in a join. You still have to know which one to pick.

Do all plants have 0001 storage location? If so, you can put where mard~lgort = 0001.

Do all the plants have storage locations that match the plant number? If so you can say mard_{lgort = marc}werks.

Is there only one storage location that has a storage bin? In that case you can say lgpbe <> ''.

You are selecting storage bin from this table and the worst case can be that all 5 or 10 storage locations that you have may have a storage bin. How can you say that the first one you picked is the right one.

So you still have to go back to whoever gave you this requirement and ask them what is the criteria to pick one storage location from among several possible ones.

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 22:00:12 GMT

Also check if your EKPO-LGORT has values, if so you can add that to the selection from MARD as mard_{lgort = ekpo}lgort.

Re: Problem with the select statement

Former Member — Thu, 22 Mar 2007 22:53:05 GMT

Srinivas

here one more problem I am facing that is

for the storage location field ( MARD-LGORT) i am getting the wrong storage location description ( T001L-LGOBE)

can u tell where exactly I am going wrong...

SIRI

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 02:52:25 GMT

Can you show your latest join statement?

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 03:01:24 GMT

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 03:11:21 GMT

I am not sure I understood that. Are you saying you want clear all the fields of the record when lgort = '0003'? In that case, do you even want to store the record in the internal table?

Just before your APPEND IT_EKKO, put a IF condition as below.

IF IT_EKKO-LGORT <> '0003'.

APPEND IT_EKKO.

CLEAR IT_EKKO.

ENDIF.

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 03:23:22 GMT

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 03:28:20 GMT

Srini,

I should out put all the records what r there ....

but when the lgort = 0003 i should not display some fields in the out put that means those fields should be empty so that when the user take the printout they should be able to enter the values on their for those records

they asked me to clear the fileds except mard_{werks, too1l}lgort, mard_{lgpbe ekko}ebeln.

just i have to keep the values from those fileds and remaining fileds i should clear...

can u guide me how to do it?

SIRI

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 03:58:50 GMT

Before you append, put this code.

IF IT_EKKO-LGORT = '0003'.

CLEAR: IT_EKKO-FIELD1, IT_EKKO-FIELD2....all fields that you want to clear.

ENDIF.

APPEND IT_EKKO.

CLEAR IT_EKKO.

Re: Problem with the select statement

Former Member — Fri, 23 Mar 2007 04:17:17 GMT

Solved this problem

Thanks alot for ur valuable time Srini....

marked the points

SIRI