topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 15 Apr 2009 08:09:53 GMT

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.

Re: select statement

Former Member — Wed, 15 Apr 2009 08:13:57 GMT

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

Re: select statement

sreeramkumar_madisetty — Wed, 15 Apr 2009 08:17:08 GMT

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

IF IT_DATA IS INITIAL.

WRITE: 'NO RECORDS'.

Regards,

Sreeram

Re: select statement

Former Member — Wed, 15 Apr 2009 08:18:09 GMT

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.

Re: select statement

Former Member — Wed, 15 Apr 2009 08:20:55 GMT

Hi,

if i remove

DELETE ADJACENT DUPLICATES FROM IT_DATA.

it is getting duplicate records.

Thanks .

Regards,

Fardeen

Re: select statement

Former Member — Wed, 15 Apr 2009 08:21:35 GMT

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

Re: select statement

Former Member — Wed, 15 Apr 2009 08:22:17 GMT

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.

Re: select statement

Former Member — Wed, 15 Apr 2009 08:23:30 GMT

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

Re: select statement

Former Member — Wed, 15 Apr 2009 08:35:18 GMT

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

Re: select statement

Former Member — Wed, 15 Apr 2009 08:37:54 GMT

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

Re: select statement

awin_prabhu — Wed, 15 Apr 2009 08:38:14 GMT

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.

Re: select statement

seshatalpasai_madala — Wed, 15 Apr 2009 08:41:04 GMT

Hi,

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

DELETE ADJACENT DUPLICATES COMPARING ....

Regards,

Sesh

Re: select statement

Former Member — Thu, 16 Apr 2009 00:57:37 GMT

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

Re: select statement

Former Member — Thu, 16 Apr 2009 04:24:04 GMT

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

Re: select statement

Former Member — Thu, 16 Apr 2009 08:30:35 GMT

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.