select statement

[Former Member]

Hi experts,

please check the following code : In the first select statement the number of record pulling up are 12. but in the second select statement only one record is pulling up however it should pull 12 records. i need to pass all the fields(MATNR,LGORT,IBLNR,ERFME,WERKS,gjahr,MAKTX,LGPBE,GEDAT )

in to a final internal table.

Thanks in advance.

SELECT A~MATNR

A~LGORT

A~IBLNR

A~ERFME

A~WERKS

a~gjahr

B~MAKTX

D~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA FROM ( ISEG AS A INNER JOIN MAKT AS B ON A_{MATNR = B}MATNR )

INNER JOIN MARD AS D ON D_{MATNR = A}MATNR

WHERE IBLNR IN R_IBLNR.

SORT IT_DATA.

DELETE ADJACENT DUPLICATES FROM IT_DATA.

IF IT_DATA IS INITIAL.

WRITE: 'NO RECORDS'.

ELSE.

SELECT GIDAT FROM IKPF

INTO TABLE IT_ikpf FOR ALL ENTRIES IN IT_DATA

WHERE lgort = IT_DATA-lgort and iblnr in R_IBLNR.

[Former Member]

Hi,

check your fields for it_data. If you forgot a key-field, you will delete all w/o one with delete duplicate.

Regards

Nicole

[Former Member]

Hi,

The problem is with the second select statement.

i have already debug the code and i think i am writing the select statement not logically correct.

Thanks for ur reply.

[Former Member]

If you have only 1 entrie left in it_data your second selection can only give you one record.

Because of this, I asked for the table definition.

Check how much entries are left insite it_data after

DELETE.........DUPLICATE.....It_data....

[Former Member]

hi

the key field should be mentioned in the second select statement so that the key field link will help you getting those values in your second table based on your first select statement.

[sreeramkumar_madisetty]

Hi

In your code simply remove the part: DELETE ADJACENT DUPLICATES FROM IT_DATA.

IF IT_DATA IS INITIAL.

WRITE: 'NO RECORDS'.

Regards,

Sreeram

[Former Member]

Hi,

if i remove

DELETE ADJACENT DUPLICATES FROM IT_DATA.

it is getting duplicate records.

Thanks .

Regards,

Fardeen

[Former Member]

Hi,

Change your 2nd SELECT to include key fields of the table:

SELECT IBLNR GJAHR GIDAT FROM IKPF
INTO TABLE IT_ikpf FOR ALL ENTRIES IN IT_DATA
WHERE lgort = IT_DATA-lgort and iblnr in R_IBLNR.

For all entries select statement will compress the result if the value is exactly the same.

Regards,

Lim...

[Former Member]

Hi,

thanks for ur reply.

Yes the FOR ALL ENTIRES is suppressing the result as the values are the same .

Can u please tell me what i should replace in my code.

Regards,

Fardeen

[seshatalpasai_madala]

Hi,

What is the type of IT_DATA, may be you should consider SORT BY and also use

DELETE ADJACENT DUPLICATES COMPARING ....

Regards,

Sesh

[Former Member]

Hi Fardeen,

To avoid compression of the result internal table, you just need:

1. To change your internal table IT_ikpf to include the key fields of IKPF, those are IBLNR and GJAHR,

2. To include key fields of the table in your SELECT for all entries statement

Change your 2nd select statement from:

SELECT GIDAT FROM IKPF
INTO TABLE IT_ikpf FOR ALL ENTRIES IN IT_DATA
WHERE lgort = IT_DATA-lgort and iblnr in R_IBLNR.

To:

SELECT IBLNR GJAHR GIDAT FROM IKPF
INTO TABLE IT_ikpf FOR ALL ENTRIES IN IT_DATA
WHERE lgort = IT_DATA-lgort and iblnr in R_IBLNR.

Regards,

Lim...

[Former Member]

Hi chang,

yes i did change the 2ns select statement and it is pulling up 2 records and first select statement is pulling up 12 records. the problem is that i need to pass these values into a final internal table. so i am looping at it_data which contain fields from 1st select statement and assigning the wa_data to wa_final.

then i am again looping at it_ikpf which contain fields from 2nd select statement. but i am getting wrong result in the output.

Thanks for ur help.

Regards,

fardeen

[Former Member]

HI Fardeen,

Instead of again looping at internal table it_ikpf,

Read table it_ikpf with key lgort = wa_data-lgort

and assign the it_ikpf values to wa_final values.

[Former Member]

hi,

when 'for all entries' is used all the primary keys must be selected

and also write

IT_DATA[] IS INITIAL.

DELETE ADJACENT DUPLICATES FROM IT_DATA comparing field1

thanks

[awin_prabhu]

Hi Fardeen,

Problem is in line 'IF IT_DATA IS INITIAL.'.

Change it to IF IT_DATA[] IS INITIAL.

SELECT A~MATNR

A~LGORT

A~IBLNR

A~ERFME

A~WERKS

a~gjahr

B~MAKTX

D~LGPBE

INTO CORRESPONDING FIELDS OF TABLE IT_DATA FROM ( ISEG AS A INNER JOIN MAKT AS B ON A_{MATNR = B}MATNR )

INNER JOIN MARD AS D ON D_{MATNR = A}MATNR

WHERE IBLNR IN R_IBLNR.

SORT IT_DATA.

DELETE ADJACENT DUPLICATES FROM IT_DATA.

IF IT_DATA IS INITIAL. <---- Change to IF IT_DATA[] IS INITIAL.

WRITE: 'NO RECORDS'.

ELSE.

SELECT GIDAT FROM IKPF

INTO TABLE IT_ikpf FOR ALL ENTRIES IN IT_DATA

WHERE lgort = IT_DATA-lgort and iblnr in R_IBLNR.