topic Select query problem in Application Development and Automation Discussions

Select query problem

Former Member — Fri, 15 Jan 2010 04:49:24 GMT

Hi,

I have a problem in my report related to my select query.

We want to make the report on plant level (we have 2 plants AAA & BBB). but when run the report it display the output for BBB plant only.

please check my code----

**************************************

SELECT DISTINCT a_{matnr b}maktx INTO CORRESPONDING FIELDS OF itab

FROM mara AS a INNER JOIN makt AS b ON a_{matnr = b}matnr

WHERE a~matnr IN matnr.

APPEND itab.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE 'NOT FOUND' TYPE 'I'(001).

ENDIF.

*********

LOOP AT itab.

SELECT labst werks FROM mard INTO (itab-labst8, itab-werks) WHERE matnr EQ itab-matnr . "AND lgort EQ 'RECP' .

MODIFY itab INDEX sy-tabix TRANSPORTING labst8 werks.

ENDSELECT.

ENDLOOP.

**************************************

But material has in both plant.

plz help..

Re: Select query problem

Former Member — Fri, 15 Jan 2010 04:53:10 GMT

Hi My Dear ABAPer,

1. You should not use select query within a loop.

2. If you want to use a select query based on where condition in another internal table, use

SELECT * FROM TABLE INTO TABLE itab1

FOR ALL ENTRIES IN ITAB

WHERE MATNR = ITAB-MATNR.

Hope it helps.

Regards,

Raj

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:04:42 GMT

I use this statement: -

SELECT * FROM MARD INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr.

but showing this error message---

The type of the database table and work area (or internal table) "ITAB1" are not Unicode convertible. .

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:10:34 GMT

Hi,

Can you please display the iternal tables declaration which you are using...

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:13:25 GMT

Hi,

Can you Tell what is the type of ITAB1. (As Unicode have many constrain related to declaration of variables and assign ment)

and if possible paste ur data declaration code.

Thanks,

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:20:49 GMT

TABLES : mara, makt, mard, mbew.

TYPE-POOLS : slis.

DATA : BEGIN OF itab OCCURS 0,

matnr LIKE mara-matnr,

werks LIKE mard-werks,

maktx LIKE makt-maktx,

lgort LIKE mard-lgort,

labst LIKE mard-labst,

insme LIKE mard-insme,

labst1 LIKE mard-labst,

labst2 LIKE mard-labst,

labst3 LIKE mard-labst,

labst4 LIKE mard-labst,

labst5 LIKE mard-labst,

labst6 LIKE mard-labst,

labst7 LIKE mard-labst,

labst8 LIKE mard-labst,

labst9 LIKE mard-labst,

total LIKE mard-labst,

vprsv LIKE mbew-vprsv,

stprs LIKE mbew-stprs,

verpr LIKE mbew-verpr,

total1 LIKE mard-labst,

END OF itab.

DATA : itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

*******************************

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

SELECT-OPTIONS : matnr FOR mara-matnr.

SELECTION-SCREEN : END OF BLOCK b1.

************************************************************************

PERFORM fill_catalog1 USING:

'MATNR' 'ITAB' 'Material No.' ,

'WERKS' 'ITAB' 'Plant' ,

'MAKTX' 'ITAB' 'Material Desc.',

'LABST8' 'ITAB' 'RECP'.

*********************************************************************************************

SELECT DISTINCT a_{matnr b}maktx INTO CORRESPONDING FIELDS OF itab

FROM mara AS a INNER JOIN makt AS b ON a_{matnr = b}matnr

WHERE a~matnr IN matnr.

APPEND itab.

ENDSELECT.

IF sy-subrc <> 0.

MESSAGE 'NOT FOUND' TYPE 'I'(001).

ENDIF.

**********************************************************************************

LOOP AT itab.

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr.

ENDLOOP.

**********************************************************************************

plz. check.....

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:32:07 GMT

Hi,

Why don't you apply parameters: Plant like mard-werks in the selection screen and then use this varibale in the select query.

Moreover try to avoid using Select and End select as it will effect the performance . Try to use into table itab clause.

Last try to aviod select statement in the in a loop again for performance measures. Try to use the for all entries outside the loop it will work properly.

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:40:35 GMT

I have changed few changes in my code...

TABLES : mara, makt, mard, mbew.

TYPE-POOLS : slis.

DATA : BEGIN OF itab OCCURS 0,

matnr LIKE mara-matnr,

werks LIKE mard-werks,

maktx LIKE makt-maktx,

lgort LIKE mard-lgort,

labst LIKE mard-labst,

insme LIKE mard-insme,

labst1 LIKE mard-labst,

labst2 LIKE mard-labst,

labst3 LIKE mard-labst,

labst4 LIKE mard-labst,

labst5 LIKE mard-labst,

labst6 LIKE mard-labst,

labst7 LIKE mard-labst,

labst8 LIKE mard-labst,

labst9 LIKE mard-labst,

total LIKE mard-labst,

vprsv LIKE mbew-vprsv,

stprs LIKE mbew-stprs,

verpr LIKE mbew-verpr,

total1 LIKE mard-labst,

END OF itab.

DATA : itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

*******************************

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

SELECT-OPTIONS : matnr FOR mara-matnr.

SELECT-OPTIONS : werks FOR mard-werks. " changed line

SELECTION-SCREEN : END OF BLOCK b1.

************************************************************************

PERFORM fill_catalog1 USING:

'MATNR' 'ITAB' 'Material No.' ,

'WERKS' 'ITAB' 'Plant' ,

'MAKTX' 'ITAB' 'Material Desc.',

'LABST8' 'ITAB' 'RECP'.

*********************************************************************************************

SELECT DISTINCT a_{matnr b}maktx INTO CORRESPONDING FIELDS OF itab

FROM mara AS a INNER JOIN makt AS b ON a_{matnr = b}matnr

WHERE a~matnr IN matnr.

APPEND itab.

ENDSELECT.

IF sy-subrc 0.

MESSAGE 'NOT FOUND' TYPE 'I'(001).

ENDIF.

**********************************************************************************

LOOP AT itab.

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr.

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr and werks in werks. " changed line

ENDLOOP.

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:42:11 GMT

Hi.

LOOP AT itab.

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr.

ENDLOOP.

This is wrong

just remove the loop and endloop.

the final will be just

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr.

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:51:28 GMT

then what would be the first query...

SELECT DISTINCT a_{matnr b}maktx INTO CORRESPONDING FIELDS OF itab

FROM mara AS a INNER JOIN makt AS b ON a_{matnr = b}matnr

WHERE a~matnr IN matnr.

APPEND itab.

ENDSELECT.

Re: Select query problem

Former Member — Fri, 15 Jan 2010 05:55:53 GMT

 TYPES : BEGIN OF TYP_itab,
matnr LIKE mara-matnr,
werks LIKE mard-werks,
maktx LIKE makt-maktx,
lgort LIKE mard-lgort,
labst LIKE mard-labst,
insme LIKE mard-insme,
labst1 LIKE mard-labst,
labst2 LIKE mard-labst,
labst3 LIKE mard-labst,
labst4 LIKE mard-labst,
labst5 LIKE mard-labst,
labst6 LIKE mard-labst,
labst7 LIKE mard-labst,
labst8 LIKE mard-labst,
labst9 LIKE mard-labst,
total LIKE mard-labst,
vprsv LIKE mbew-vprsv,
stprs LIKE mbew-stprs,
verpr LIKE mbew-verpr,
total1 LIKE mard-labst,
END OF TYP_itab.

DATA : ITAB TYPE TABLE OF TYP_ITAB,
             WA_ITAB TYPE TYP_ITAB.

I have modified your data declarartion. Try like this.

Regards,

Raj

Re: Select query problem

Former Member — Fri, 15 Jan 2010 06:17:41 GMT

Hi,

Try to use Plant parameter instead of Select options and use it in the select query only then you will be able to achieve this functionality.

Here is the example of it:-

SELECT * FROM mard INTO TABLE itab1 FOR ALL ENTRIES IN itab WHERE matnr = itab-matnr and werks = werks. " changed line

Please donot repeat the select query again in the loop as you will not able to achieve the funtionality.

You can write up select query again as by taking data form MARA,MARD using inner joins and then you can use select single statement using the MAKT table later in the loop. You will able to understand your logic of the code more clearly and moreover try to use work area and types decalration in the code.....

Edited by: nav009 on Jan 15, 2010 7:22 AM