topic Re: Select Query Problem in Application Development and Automation Discussions

Select Query Problem

Former Member — Thu, 18 Feb 2010 05:52:10 GMT

Hi,

My Report is working fine but we want report on basis of sales office wise..means Branch employee can see only own data not of other branches.

My query is on VBRK and picking the Billing document from there. for Sales office wise data, we must goto VBRP table...

I query on VBRP and in selection screen put the Sales office input criteria.

But when i am inserting 1 billing document of AAA Sales office, but in sales office inserting BBB Sales office, then the output shows. It must not show because billing document is of AAA Sales office not BBB.

My Query is..

SELECT * FROM vbrk WHERE vbeln IN vbeln AND ( fkart = 'ZG1' OR fkart = 'ZG2' OR fkart = 'RE' OR fkart = 'ZWCN' OR fkart = 'G2' ) AND rfbsk EQ 'C'.

IF sy-subrc <> 0.

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

ENDIF.

LOOP AT itab.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.

ENDLOOP.

and in

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

PERFORM write_line_items.

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

FORM write_line_items.

SELECT * INTO CORRESPONDING FIELDS OF TABLE vbrptab FROM vbrp WHERE

vbeln = vbrk-vbeln .

DESCRIBE TABLE vbrptab LINES total_records.

half_total_records = total_records / 2.

DO half_total_records TIMES.

READ TABLE vbrptab INDEX sy-index.

MOVE vbrptab-vbeln TO itab-vbeln.

MOVE vbrptab-matnr TO itab-matnr.

MOVE vbrptab-fkimg TO itab-fkimg.

MOVE vbrptab-netwr TO itab-netwr1.

MOVE vbrptab-mwsbp TO itab-mwsbp1.

MOVE vbrptab-vkbur TO itab-vkbur.

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

IF vbrp-vkbur = itab-vkbur.

MODIFY itab.

ELSE.

ENDIF.

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

debit_line_no = sy-index + half_total_records.

READ TABLE vbrptab INDEX debit_line_no.

MOVE vbrptab-netwr TO itab-netwr2.

MOVE vbrptab-mwsbp TO itab-mwsbp2.

itab-netwr2 = itab-netwr2 * ( -1 ).

itab-mwsbp2 = itab-mwsbp2 * ( -1 ).

itab-diff1 = itab-netwr1 - itab-netwr2.

itab-diff2 = itab-mwsbp1 - itab-mwsbp2.

itab-total = itab-diff1 + itab-diff2.

IF itab-total NE 0.

APPEND itab.

ENDIF.

CLEAR itab.

ENDDO.

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

plz. help..

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 06:03:26 GMT

In condition you have specify the sales office.

[Your Previous post|;

ex:

loop at itab into wa.

read table it_ab int wa_ab with key sales_off = wa_sales_office ('AAA').

if sy-subrc = 0.

process.

endif..

endloop.

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 06:27:02 GMT

I have done some modifications: -

DATA : BEGIN OF wa OCCURS 0.

INCLUDE STRUCTURE itab.

DATA : END OF wa.

LOOP AT itab INTO wa.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.

READ TABLE itab into wa WITH KEY vkbur = wa-vkbur.

IF sy-subrc = 0.

*process.

ENDIF..

ENDLOOP.

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

Not work..

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 06:40:13 GMT


> I have done some modifications: - 
> 
> DATA : BEGIN OF wa OCCURS 0.
>         INCLUDE STRUCTURE itab.
> DATA : END OF wa.
> 
>   LOOP AT itab INTO wa.
>     SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.
> 
>     READ TABLE itab into wa WITH KEY vkbur = wa-vkbur.
>     IF sy-subrc = 0.
> 
> *process.
> ENDIF..
> ENDLOOP.
> **************************
> 
> Not work..

I have u some example only.

Please check Your logic and this coding is an example only.

types : BEGIN OF ty_str.

matnr type matnr

fkimg ...

netwr....

mwsbp....

vkbur......

types : END OF ty_str.

data:itab type table of ty_str,
     wa type ty_str.
parameter:p_vbeln for vbak-vbeln.
SELECT matnr fkimg netwr mwsbp vkbur FROM vbrp 
INTO table itab WHERE vbeln = p_vbeln.
LOOP AT itab INTO wa where vkbur = 'AAA'.
*process.
ENDIF..
ENDLOOP.

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 06:43:52 GMT

Hi,

//LOOP AT itab.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.

ENDLOOP.

//AND vkbur IN vkbur -> check here

how is ur sales office restricted in the above select from VBRP .. Check the where condition to restrict the sales office .. i hope vbkur is relating to sales office ..

Progam logic needs to be refined for select and read statements .

Br,

Vijay.

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 06:54:44 GMT

Dear ABAPER,

Use For all entries statement to fetch data from VBRP table rather then using select single statement.

Example

Your code

LOOP AT itab INTO wa.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.

READ TABLE itab into wa WITH KEY vkbur = wa-vkbur.

IF sy-subrc = 0.

*process.

ENDIF..

ENDLOOP.

You can modify your code as follows

if not itab[] is initial.

Select matnr fkimg netwr mwsbp vkbur from vbrp

into table itab2

for all entries in itab

where vbeln = itab-vbeln

and vkbur = 'AAA'

endif.

loop at itab into wa_itab.

read table itab2 with ky vbeln = itab-vbeln and vkbur = 'AAA' (optional because itab2 has all the records with 'AAA' sales office)

if sy-subrc = 0.

your logic

endif.

endloop.

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 07:18:50 GMT

Is there any problem with this code---

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

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

SELECT-OPTIONS: vbeln FOR vbrk-vbeln.

SELECT-OPTIONS: vkbur FOR vbrp-vkbur.

SELECTION-SCREEN END OF BLOCK b1.

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

LOOP AT itab.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln and vkbur in vkbur.

ENDLOOP.

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

Your comments are on my where condition - vkbur in vkbur.

Please see in selection screen, if i insert a sales office in my selection screen, then that sales office value should come in report output. That's why i make this where condition...vkbur in vkbur.

plz. suggest me, if this not then what should be there...

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 07:30:39 GMT

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

> SELECT-OPTIONS: vbeln FOR vbrk-vbeln.

> SELECT-OPTIONS: vkbur FOR vbrp-vkbur.

> SELECTION-SCREEN END OF BLOCK b1.

> LOOP AT itab.

> SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab-matnr, itab-fkimg, itab-netwr1, itab-mwsbp1, itab-vkbur) WHERE vbeln = itab-vbeln and vkbur in vkbur.

> ENDLOOP.

> *******************************************************************

No need for loop at itab,

select single means you are featching one record.

coding is correct.

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 07:37:10 GMT

If my code is correct then what should be the problem in my code...

as per ur earlier replied. that will help in my case...

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 07:42:56 GMT

TABLES:vbak,vbrp.
*******************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: vbeln FOR vbak-vbeln.
SELECT-OPTIONS: vkbur FOR vbrp-vkbur.
SELECTION-SCREEN END OF BLOCK b1.
*******************************************************************
TYPES : BEGIN OF ty_str,
matnr TYPE matnr ,
fkimg TYPE fkimg,
netwr TYPE netwr,
mwsbp TYPE mwsbp,
vkbur TYPE  vkbur,
 END OF ty_str.

DATA:itab TYPE ty_str.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO
  itab
   WHERE vbeln IN vbeln
   AND vkbur IN vkbur.
IF sy-subrc = 0.
ENDIF.

Sorry i try to edit my previous post but some network error.please check this code

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 07:55:43 GMT

i changed my code as per ur suggestion...

TYPES : BEGIN OF ty_str,

matnr TYPE matnr ,

fkimg TYPE fkimg,

netwr1 TYPE netwr,

mwsbp1 TYPE mwsbp,

vkbur TYPE vkbur,

END OF ty_str.

DATA:itab1 TYPE ty_str.

SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab1-matnr, itab1-fkimg, itab1-netwr1, itab1-mwsbp1, itab1-vkbur) WHERE vbeln = itab-vbeln AND vkbur IN vkbur.

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

But not work..

Re: Select Query Problem

Former Member — Thu, 18 Feb 2010 08:56:15 GMT


> i changed my code as per ur suggestion...
> 
> TYPES : BEGIN OF ty_str,
> matnr TYPE matnr ,
> fkimg TYPE fkimg,
> netwr1 TYPE netwr,
> mwsbp1 TYPE mwsbp,
> vkbur TYPE  vkbur,
>  END OF ty_str.
> 
> DATA:itab1 TYPE ty_str.
> 
> SELECT SINGLE matnr fkimg netwr mwsbp vkbur FROM vbrp INTO (itab1-matnr, itab1-fkimg, itab1-netwr1, itab1-mwsbp1, itab1-vkbur) WHERE vbeln = itab-vbeln 
*(VBELN IN VBELN)AND vkbur IN vkbur.
if sy-subrc = 0.
message 'data available' type 'I'.
else.
message 'data not found' type 'E'.
endif.
> 
> *****************
> But not work-->..

In were condition vbeln in vbeln .Change it .

What type of error you are getting if no error debug the progam and check the inputs