Error in Select statement

[Former Member]

Hi:

My selection-screen fields are:

date range, work order type range ( auart) , functional location range ( tplnr) and abc indicator( abckz).

I need to retrieve AUART , ARBEI, ISMNW from AUFK, AFRU, AFVV .

I tried in the following way:

SELECT a_{auart b}arbei c~ismnw INTO TABLE IT_TAB

FROM AUFK AS a

INNER JOIN PLPO as b ON a_{werks = b}werks

INNER JOIN AFRU as c ON a_{werks = c}werks

WHERE a~auart IN S_AUART.

its goin to short dump.

I would appreciate if anyone can correct me on this.

Thanks.

Raghu.

[Former Member]

Waht was the cause of the dump?

Rob

[Former Member]

I guess it's timing out because neither the WHERE nor the JOIN conditions use any index.

Rob

[Former Member]

Hi Rob,

can u give a sample code for using the index in my scenario.

thx

RAGHU

[Former Member]

hi Raghu,

There is no key field in the where condition of the select statement. Due to large amount of data, if you don't provide key field in the select statement program will give dump.

Regards,

Venkata Narayana

[Former Member]

try this

SELECT a_{auart b}arbei c~ismnw INTO <b>CORRESPONDING FIELDS OF</b> TABLE IT_TAB

FROM AUFK AS a

INNER JOIN PLPO as b ON a_{werks = b}werks

INNER JOIN AFRU as c ON a_{werks = c}werks

WHERE a~auart IN S_AUART.

I think in your internal table the fields sequence are not same as select query.

regards

shiba dutta

[former_member186741]

If you cannot streamline your select and really do have to retrieve a lot of data you can make the following change to force it to operate in bite sized chunks.

SELECT a_{auart b}arbei c~ismnw INTO TABLE <b>IT_TAB2</b>

<b>PACKAGE SIZE 1000</b>

FROM AUFK AS a

INNER JOIN PLPO as b ON a_{werks = b}werks

INNER JOIN AFRU as c ON a_{werks = c}werks

WHERE a~auart IN S_AUART.

<b>

IF sy-subrc EQ 0.

append lines of IT_TAB2 TO IT_TAB.

ENDIF.

ENDSELECT.</b>

[Former Member]

Hi,

Instead of giving or comparing the fields a_{werks = c}werks compare b_{werks = c}werks.And give a key field so tht it wont end up in short dump.

regards,

madhuri.

[former_member196280]

The main cause for short dump is you did not use any key fields and it is getting time out.

In this case I recommend nested-loops. It may work effectively than joins....

Reward points if useful.

Regards,

SaiRam

[Former Member]

Hi :

I modified my select statement as

SELECT a_{auart b}ismnw INTO TABLE IT_TAB

FROM AUFK AS a

INNER JOIN AFRU as b ON a_{werks = b}werks

WHERE a~auart IN S_AUART

AND A~WERKS = '1000'.

its giving run time errors " either conversion is not supported for the target field's type or target field is too short to accept the value".

I am trying to convert ISMNW field to char type, since its type is QUAN.

Thanks.

Raghu

[Former Member]

Hi Raghu ,

How have you defined the internal table IT_TAB.

Regards

Arun

[former_member186741]

Your definition of the ismnw field in IT_TAB must be too small. Either make it bigger or change it to match the database definition and do the conversion later on outside the sql.

[Former Member]

Internal table is declared.

convert ismnw field to char type from quan, it that u mean.

[former_member186741]

show us exactly how you have defined the table. Show us the abap code.

[Former Member]

In general, how can we select ISMNW , ARBEI fields.we can find these fields in afru, afvv and plpo tables.

[Former Member]

Hi Raghu ,

What i ment was could you please paste the code where you declare your internal table , what i understand is that the error occurs because the length of each row of the internal table is not sufficient to store the data you are retreiving.

Pasting your decleration here will help us to understand the root casue in a better way.

Regards

Arun

[former_member186741]

in your internal table declare the ISMNW field as

ismnw type afru-ismnw,

[Former Member]

here is the code:

REPORT z_ska_scr NO STANDARD PAGE HEADING " LINE-SIZE 132.

MESSAGE-ID z_ka.

*-Tables----

---

TABLES: aufk, iloa,hivg,tpmus,afru,plpo.

*-Selection screen----

---

SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.

SELECT-OPTIONS:

s_auart FOR aufk-auart, "Work Order type

s_tplnr FOR iloa-tplnr, "Functional Location

s_abckz FOR iloa-abckz, "ABC Indicator

s_date FOR sy-datum. "Date Range

SELECTION-SCREEN END OF BLOCK blk1.

*-Global ypes----

---

DATA :BEGIN OF it_tab OCCURS 0,

auart TYPE auart,

ismnw type afru-ismnw,

arbei TYPE plpo-arbei,

tplnr TYPE tplnr,

abckz TYPE abckz,

werks(10) TYPE c VALUE '1000',

END OF it_tab.

*INITIALIZATION.

*

*

*-Main Program ogic----

---

START-OF-SELECTION.

SELECT a_{auart b}ismnw INTO TABLE it_tab

FROM aufk AS a

INNER JOIN afru AS b ON a_{werks = b}werks

WHERE a~auart IN s_auart

AND a~werks = '1000'.

IF sy-subrc <> 0.

MESSAGE i000.

RETURN.

ENDIF.

LOOP AT it_tab.

WRITE 😕 it_tab-auart, it_tab-ismnw.

ENDLOOP.

thanks.

Raghu

[former_member186741]

I believe you have to redesign you select statement. The join is too simple....only joining ON a_{werks = b}werks.....this will link every aufk's at the site 1000 to every afru at site 1000 regardless of if they are on the same order. I think this will cause a time-out as too many matches will happen.

[Former Member]

thats only because of select statement or do i need to convert ISMNW , ARBEI fields into char type from quan type

[former_member186741]

I think the format of the fields is your smallest problem. I don't think the select itself makes sense. It joins EVERY AUFK to EVERY AFRU just on plant. That means if you have 500 afru's and 500 aufk's for site 1000 you will get 500 * 500 rows reurned: 250,000..... In my test system one site has 1220 rows in AFRU and 3000 rows in AUFk.........your sql would return 1220 * 3000 = 3,660,000 rows. Speak to someone at your site about what you are trying to do.

[Nawanandana]

hai ragu

SELECT a_{auart b}arbei c~ismnw into corresponding fields of table IT_TAB

FROM AUFK AS a

INNER JOIN PLPO as b ON b_{werks = a}werks

INNER JOIN AFRU as c ON c_{werks = a}werks

WHERE a~auart IN S_AUART.

i think u can do it now

regard

nawa