topic Re: How to Retriev data from database view through select statement? in Application Development and Automation Discussions

How to Retriev data from database view through select statement?

Former Member — Mon, 21 Jun 2010 07:31:12 GMT

Need code to Retrieve data from database view through select statement

REPORT zfi_v1.

tables : bkpf,bseg.

TYPES : BEGIN OF ty_vbsegs,

belnr TYPE belnr_d,

gjahr TYPE gjahr,

bukrs TYPE bukrs,

buzei TYPE buzei,

bschl TYPE bschl,

mwskz TYPE mwskz,

wrbtr TYPE wrbtr,

sgtxt TYPE sgtxt,

saknr TYPE saknr,

blart TYPE blart,

budat TYPE budat,

xblnr TYPE xblnr1,

END OF ty_vbsegs.

DATA : it_vbsegs TYPE TABLE OF ty_vbsegs,

wa_vbsegs TYPE ty_vbsegs.

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

PARAMETERS : p_bukrs TYPE bseg-bukrs.

PARAMETERS : p_hkont TYPE bseg-hkont.

SELECT-OPTIONS : s_budat FOR bkpf-budat OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK b1 .

START-OF-SELECTION.

BREAK-POINT.

SELECT belnr

gjahr

bukrs

buzei

bschl

mwskz

wrbtr

sgtxt

saknr

blart

budat

xblnr

FROM v_vbsegs

INTO TABLE it_vbsegs

WHERE bukrs = p_bukrs

AND saknr = p_hkont

AND budat IN s_budat.

END-OF-SELECTION.

LOOP AT it_vbsegs INTO wa_vbsegs.

WRITE:/ wa_vbsegs-belnr,

wa_vbsegs-gjahr,

wa_vbsegs-bukrs,

wa_vbsegs-buzei,

wa_vbsegs-bschl,

wa_vbsegs-mwskz,

wa_vbsegs-wrbtr,

wa_vbsegs-sgtxt,

wa_vbsegs-saknr,

wa_vbsegs-blart,

wa_vbsegs-budat,

wa_vbsegs-xblnr.

CLEAR : wa_vbsegs.

ENDLOOP.

At run time we are not getting data into the internal table( created on database view)

Re: How to Retriev data from database view through select statement?

SuhaSaha — Mon, 21 Jun 2010 07:38:51 GMT

Check if the data exists in the view for the WHERE condition you have mentioned.

Re: How to Retriev data from database view through select statement?

Former Member — Mon, 21 Jun 2010 08:16:08 GMT

Hi ,

You can treat the dat base view as table only. So the way we retrieve datya from tables using select query , same way you can retrieve data from data bsea view. Just see whether ther data ixists with tye conditions you have mentioned.

Hope this is helpful.

Thanks & Regards,

Uma Dave

Re: How to Retriev data from database view through select statement?

Former Member — Wed, 23 Jun 2010 07:45:14 GMT

Can u plz suggest some standard database views for bseg and bkpf?

Re: How to Retriev data from database view through select statement?

RaymondGiuseppi — Wed, 23 Jun 2010 07:54:04 GMT

Check via SE11, v_vbsegs is not a database view on BKPF and BSEG (but only refers to Document Parking).

BSEG is a cluster table (it does not exist on the database as a separate table, but is a part of table RFBLG), so it is not possible to create a database view. As BSEG is a cluster table, SELECT can only be optimized when specifying some of the actual database key (RFBLG : MANDT, BUKRS, BELNR, GJAHR)

You can try to use one of the "secondary indexes" maintained by SAP

- BSAD Accounting: Secondary Index for Customers (Cleared Items)

- BSAK Accounting: Secondary Index for Vendors (Cleared Items)

- BSAS Accounting: Secondary Index for G/L Accounts (Cleared Items)

- BSID Accounting: Secondary Index for Customers

- BSIK Accounting: Secondary Index for Vendors

- BSIM Secondary Index, Documents for Material

- BSIP Index for Vendor Validation of Double Documents

- BSIS Accounting: Secondary Index for G/L Accounts

Regards,

Raymond

Re: How to Retriev data from database view through select statement?

Former Member — Wed, 23 Jun 2010 08:15:01 GMT

While retrieving data from Bseg its taking time to gte data from bseg can u suggest another table instead of BSEG?

While using this select statement for bseg we are getting performance issue can u suggest anyway to improve performance?

SELECT bukrs "Company Code

belnr "Accounting Document Number

gjahr "Fiscal Year

buzei "Number of Line Item Within Accounting Document

bschl "Posting Key

mwskz "Sales Tax Code

wrbtr "Amount in document currency

sgtxt "Item Text

hkont "General Ledger Account

lifnr "Account Number of Vendor or Creditor

FROM bseg "Document Segment for Document Parking - G/L Account Database

INTO TABLE it_bseg_1

WHERE bukrs = p_bukrs

AND hkont = p_hkont.

Re: How to Retriev data from database view through select statement?

Former Member — Wed, 23 Jun 2010 08:28:22 GMT

See these

ARIX_BKPF FI_DOCUMNT: Header-Oriented Document Index

BKPF Accounting Document Header

BKPF_ADD Accounting Document Header

EBKPF Fin.Accntng Doc.Header (of Docs from Exter

LRBKPF Documents as Result of Logical Document Re

TXW_S_BKPF Retrieve BKPF from archive

VBKPF Document Header for Document Parking

VIAKBKPF Document Header from Service Charge Settle

VBSEGA Document Segment for Document Parking - As

VBSEGD Document Segment for Customer Document Par

VBSEGK Document Segment for Vendor Document Parki

VBSEGS Document Segment for Document Parking - G/

VIAKBSEG Document Line Item from SCS for Interface

Regards,

Uma Dave

Re: How to Retriev data from database view through select statement?

RaymondGiuseppi — Wed, 23 Jun 2010 13:57:15 GMT

Try to firsy select data from tables BSIS and BSAS as your criteria is General ledger account (only BSIS if account is not clearable)

If some fields are missing, you can then SELECT from BSEG with the full key (BUKRS, BELNR GJAHR and BUZEI) in a SELECT FOR ALL ENTRIES

Regards,

Raymond

Re: How to Retriev data from database view through select statement?

Former Member — Wed, 23 Jun 2010 17:50:09 GMT

Moderator message - Please search before asking - post locked Rob