topic Re: Error in Select statement in Application Development and Automation Discussions

Error in Select statement

Former Member — Thu, 07 Jun 2007 21:02:52 GMT

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.

Re: Error in Select statement

Former Member — Thu, 07 Jun 2007 21:15:15 GMT

Waht was the cause of the dump?

Rob

Re: Error in Select statement

Former Member — Thu, 07 Jun 2007 21:23:51 GMT

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

Rob

Re: Error in Select statement

Former Member — Thu, 07 Jun 2007 21:29:20 GMT

Hi Rob,

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

thx

RAGHU

Re: Error in Select statement

Former Member — Thu, 07 Jun 2007 21:33:43 GMT

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

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 01:23:51 GMT

try this

SELECT a_{auart b}arbei c~ismnw INTO CORRESPONDING FIELDS OF 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

Re: Error in Select statement

former_member186741 — Fri, 08 Jun 2007 01:35:50 GMT

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 IT_TAB2

PACKAGE SIZE 1000

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.

IF sy-subrc EQ 0.

append lines of IT_TAB2 TO IT_TAB.

ENDIF.

ENDSELECT.

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 02:29:27 GMT

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.

Re: Error in Select statement

former_member196280 — Fri, 08 Jun 2007 02:33:04 GMT

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

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 04:47:44 GMT

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

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 04:50:37 GMT

Hi Raghu ,

How have you defined the internal table IT_TAB.

Regards

Arun

Re: Error in Select statement

former_member186741 — Fri, 08 Jun 2007 04:50:52 GMT

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.

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 04:57:22 GMT

Internal table is declared.

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

Re: Error in Select statement

former_member186741 — Fri, 08 Jun 2007 05:00:00 GMT

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

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 05:00:15 GMT

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

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 05:01:21 GMT

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

Re: Error in Select statement

former_member186741 — Fri, 08 Jun 2007 05:04:09 GMT

in your internal table declare the ISMNW field as

ismnw type afru-ismnw,

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 05:05:46 GMT

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

Re: Error in Select statement

former_member186741 — Fri, 08 Jun 2007 05:19:59 GMT

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.

Re: Error in Select statement

Former Member — Fri, 08 Jun 2007 05:29:46 GMT

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