time out while fetching data from bkpf

[former_member420959]

hello all

i need to get data from bkpf based on values from ekbe.

i am using the following link

SELECT   BUKRS

              BELNR

              GJAHR

              BLART

              BUDAT

              BLDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_EKBE

              WHERE BUKRS IN S_BUKRS

               AND   GJAHR = T_EKBE-GJAHR

              AND   BLART IN ('RE', 'WE')

              AND   AWKEY = T_EKBE-AWKEY.

but this statement is leading to time out

i searched results for this topic previously from this forum and few suggested the link

ekbe-ebeln < = > rseg-ebeln

ekbe-ebelp < = > rseg-ebelp

and then

rseg-belnr < = > bkpf-belnr

rseg-gjahr < = > bkpf-gjahr

but no data is fetched into belnr on this basis.

SELECT   BUKRS

              BELNR

              GJAHR

              BLART

              BUDAT

              BLDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_RSEG

              WHERE BUKRS IN S_BUKRS

              AND   BELNR = T_RSEG-BELNR

              AND   GJAHR = T_RSEG-GJAHR

              AND   BLART IN ('RE', 'WE').

please help me out.

Thanks in advance

ujwal

[former_member420959]

hello all

i need to get data from bkpf based on values from ekbe.

i am using the following link

SELECT   BUKRS

              BELNR

              GJAHR

              BLART

              BUDAT

              BLDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_EKBE

              WHERE BUKRS IN S_BUKRS

               AND   GJAHR = T_EKBE-GJAHR

              AND   BLART IN ('RE', 'WE')

              AND   AWKEY = T_EKBE-AWKEY.

but this statement is leading to time out

i searched results for this topic previously from this forum and few suggested the link

ekbe-ebeln < = > rseg-ebeln

ekbe-ebelp < = > rseg-ebelp

and then

rseg-belnr < = > bkpf-belnr

rseg-gjahr < = > bkpf-gjahr

but no data is fetched into belnr on this basis.

SELECT   BUKRS

              BELNR

              GJAHR

              BLART

              BUDAT

              BLDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_RSEG

              WHERE BUKRS IN S_BUKRS

              AND   BELNR = T_RSEG-BELNR

              AND   GJAHR = T_RSEG-GJAHR

              AND   BLART IN ('RE', 'WE').

please help me out.

Thanks in advance

ujwal

[RaymondGiuseppi]

Once you get an EKBE internal table, build the key to access BKPF by filling reference fields

- AWTYP = 'RMRP' for MM invoices, 'MKPF' for movements (from EKBE-VGABE, look at domain values)

- AWKEY = BELNR+GJAHR  (Use a structure like MKPF_AWKEY to map)

- AWSYS = Usually ignore this key or space

Then SELECT from BKPF using only those fields (index BKPF~4 will be used)

Regards,

Raymond

[Former Member]

Hi Ujwal,

     Check the code hope it will works...

SELECT     EBELN

    EBELP

    ZEKKN

    GJAHR

    BELNR

    BUZEI

    FROM EKBE

    INTO TABLE <T_EKBE>

    WHERE <CONDITIONS YOU WANT>.

SELECT  BELNR

    GJAHR   

    BUKRS

        FROM RSEG

        INTO TABLE T_RSEG

        FOR ALL ENTRIES IN T_EKBE

        WHERE BELNR = T_EKBE-BELNR

    AND   GJAHR = T_EKBE-GJAHR

    AND   BUKRS IN S_BUKRS

    AND   EBELN = T_EKBE-EBELN

    AND   EBELP = T_EKBE-EBELP

    AND   ZEKKN = T_EKBE-ZEKKN

    AND   BUZEI = T_EKBE-LFPOS.

SELECT  BUKRS

              BELNR

              GJAHR

              BLART

              BLDAT

              BUDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_EKBE

              WHERE BUKRS = T_EKBE-BUKRS

              AND   BELNR = T_EKBE-BELNR

              AND   GJAHR = T_EKBE-GJAHR

              AND   BLART IN ('RE', 'WE')

              AND   AWKEY = T_EKBE-AWKEY.

[Former Member]

Another thing if it doesn't works then i need to know what are your inputs from selection screen....?

[Former Member]

Hi ujwal,

Why dont you include the field 'BELNR' (one of the primary keys of BKPF)in the where clause of the first select statement.

You could do it as.

SELECT   BUKRS

              BELNR

              GJAHR

              BLART

              BUDAT

              BLDAT

              XBLNR

              AWKEY

              FROM BKPF

              INTO TABLE T_BKPF

              FOR ALL ENTRIES IN T_EKBE

              WHERE BUKRS IN S_BUKRS

               AND BELNR = T_EKBE-BELNR

               AND   GJAHR = T_EKBE-GJAHR

               AND   BLART IN ('RE', 'WE')

               AND   AWKEY = T_EKBE-AWKEY.

Then time out may be because of the time constraints for you on this program.

It is decided by BASIS i guess.(BKPF will have very large data)

[Former Member]

Before going to Write a SQL query better go to transaction 'SQVI' (Quick Viewer), follow the steps...

1. Click on Create (To Create a Quick view).

2. Provide Description.

3. Select the data source as Table join by default Table will comes change it to Table join.

4. Select the mode as Basic Mode.

5. Click on Insert table Button on Application toolbar  "".

6. Enter the table name there for say EKBE.

7. Then add another table RSEG.

8. Then add the other table BKPF.

9. There you can see the tables how they are linked with keys.

10. In this way, you can write SQL statement perfectly.

In the previous post i done a mistake  see the following code

SELECT     EBELN

    EBELP

    ZEKKN

    GJAHR

    BELNR

    BUZEI

    FROM EKBE

    INTO TABLE <T_EKBE>

    WHERE <CONDITIONS YOU WANT>.

SELECT  BELNR

    GJAHR   

    BUKRS

        FROM RSEG

        INTO TABLE T_RSEG

        FOR ALL ENTRIES IN T_EKBE

        WHERE BELNR = T_EKBE-BELNR

    AND   GJAHR = T_EKBE-GJAHR

    AND   BUKRS IN S_BUKRS

    AND   EBELN = T_EKBE-EBELN

    AND   EBELP = T_EKBE-EBELP

    AND   ZEKKN = T_EKBE-ZEKKN

    AND   BUZEI = T_EKBE-LFPOS.

SELECT  BUKRS

        BELNR

        GJAHR

        BLART

        BLDAT

    BUDAT

        XBLNR

        AWKEY

        FROM BKPF

        INTO TABLE T_BKPF

        FOR ALL ENTRIES IN T_RSEG

        WHERE BUKRS = T_EKBE-BUKRS

    AND   BELNR = T_EKBE-BELNR

        AND   GJAHR = T_EKBE-GJAHR

        AND   BLART IN ('RE', 'WE')

        AND   AWKEY = T_EKBE-AWKEY.

[former_member420959]

hi sudheer,

can we write for all entries on one table and use condition of other.

in the above statement it is saying

t_ekbe is table without header line so it doesnot contain field bukrs.

Thanks

ujwal

[RaymondGiuseppi]

Of course, not. Here as T_EKBE is not the internal table of the option FOR ALL ENTRIES, Open SQL look for a variable active at this step of the program. Here as you are lucky and the internal table has no header, it raise an error. Where you unlucky the Open SQL would have used the header values in the WHERE clause.

Regards,

Raymond

[Former Member]

Here see i used the elect options field S_BUKRS for that field in the SQL statement.

might you specified like this

SELECT  BELNR

              GJAHR   

              BUKRS

              FROM RSEG

             INTO TABLE T_RSEG

             FOR ALL ENTRIES IN T_EKBE

             WHERE BELNR = T_EKBE-BELNR

              AND   GJAHR = T_EKBE-GJAHR

              AND   BUKRS IN T_EKBE-BUKRS

              AND   EBELN = T_EKBE-EBELN

              AND   EBELP = T_EKBE-EBELP

              AND   ZEKKN = T_EKBE-ZEKKN

              AND   BUZEI = T_EKBE-LFPOS.

Observe the bold letters instead you have use

SELECT  BELNR

    GJAHR   

    BUKRS

        FROM RSEG

        INTO TABLE T_RSEG

        FOR ALL ENTRIES IN T_EKBE

        WHERE BELNR = T_EKBE-BELNR

    AND   GJAHR = T_EKBE-GJAHR

   AND   BUKRS IN S_BUKRS

    AND   EBELN = T_EKBE-EBELN

    AND   EBELP = T_EKBE-EBELP

    AND   ZEKKN = T_EKBE-ZEKKN

    AND   BUZEI = T_EKBE-LFPOS.

[former_member420959]

hi raymond,

can u please provide me with sample select in this senario

i am confused with where conditions to use here

Thanks in advance

ujwal

[Former Member]

The second SQL statement Works properly but the 1st SQL doesn't works...

[former_member420959]

hi sudheer,

the problem came not when fetching data to t_rseg but while fetching into t_bkpf when i did as u said

in that select statement

SELECT  BUKRS

        BELNR

        GJAHR

        BLART

        BLDAT

    BUDAT

        XBLNR

        AWKEY

        FROM BKPF

        INTO TABLE T_BKPF

        FOR ALL ENTRIES IN T_RSEG

        WHERE BUKRS = T_EKBE-BUKRS

    AND   BELNR = T_EKBE-BELNR

        AND   GJAHR = T_EKBE-GJAHR

        AND   BLART IN ('RE', 'WE')

        AND   AWKEY = T_EKBE-AWKEY.

u wrote for all entries for t_rseg and in the where statement u are using t_ekbe

so its throwng error as t_ekbe is table without header line so it doesnot have a field bukrs

So,please to me right way to get data into t_bkpf as now we have data in t_ekbe and t_rseg

Thanks

ujwal

[Former Member]

Sorry Ujwal, I miss typed the query

see the code below

SELECT  BUKRS

        BELNR

        GJAHR

        BLART

        BLDAT

    BUDAT

        XBLNR

        AWKEY

        FROM BKPF

        INTO TABLE T_BKPF

        FOR ALL ENTRIES IN T_RSEG

        WHERE BUKRS = T_RSEG-BUKRS

    AND   BELNR = T_RSEG-BELNR

        AND   GJAHR = T_RSEG-GJAHR

        AND   BLART IN ('RE', 'WE').

[former_member420959]

I tried the above query

but no data is getting fetched into t_bkpf

the values in rseg-belnr are not in bkpf-belnr

is the link correct??

as i said the select statement which i mentioned in my starting post using awkey data is coming but in prouction due to huge data it is going to time out

[Former Member]

Better ask your consultant about the navigation flow.

Take a test data, i mean sit with the functional consultant and see how fetching the data from tables.

get clarify with the scenario ones...

[shadab_maldar]

Hi Ujwal,

You can also create a database view and fetch the data from it, Try with this it may help.

Hello Raymond,

Not sure, am I correct.

Regards,

Shadab.

[RaymondGiuseppi]

The problem is the AWKEY = BELNR+GJAHR which prevents open-sql JOIN