01-05-2015 9:34 AM
Dear All,
One of the report is working fine in development and Quality system, But not in production.
Here in production system 1st select statement getting the data from Z table and 2nd select statement not getting any data from BKPF table as mention in where conditions.
And we are getting 2 errors in code inspector like:
1. Large Table ziftr_reqlog_adp: No first field of a table index in where condition
2.Large Table bkpf: No first field of a table index in where condition
Here I attached my Ztable fields screen and below mention code is written in my report:
SELECT uniqid
ldate
legacy_id
docnumber FROM ziftr_reqlog_adp
INTO TABLE it_reqlog_adp
WHERE ldate IN s_ldate AND
docnumber NE ''.
IF NOT it_reqlog_adp[] IS INITIAL.
SORT it_reqlog_adp BY ldatedocnumber.
*******Fetching data from BKPF with entries in ziftr_reqlog_adp
SELECT bukrs
belnr
gjahr
blart
bldat
budat
cpudt
xblnr
bktxt
waers
kursf
hwaer FROM bkpf INTO TABLE it_bkpf
FOR ALL ENTRIES IN it_reqlog_adp
WHERE ( belnr = it_reqlog_adp-docnumber ) AND
( blart = 'I4' OR blart = 'I5' ) AND
( cpudt IN s_ldate ) .
SORT it_bkpf BY bukrs belnr cpudt.
ENDIF.
Please share your valuable suggestions.
Regards
Rajasekhar S
01-05-2015 12:42 PM
Dear All,
Thanx for your quick responses,
As per above suggestions, i have been used first field in where condition in both select statements and Code inspector 2 error has been resolved as mention in below:
But still i am unable to run the report in production system.
1st select statement getting the data from Z table and 2nd select statement not getting any data from BKPF table as mention in where conditions.
SELECT ind
uniqid
ldate
legacy_id
docnumber FROM ziftr_reqlog_adp
INTO TABLE it_reqlog_adp
WHERE ind EQ 'DOCP' AND
ldate IN s_ldate AND
docnumber NE ''.
SORT it_reqlog_adp BY docnumber ldate.
IF NOT it_reqlog_adp[] IS INITIAL.
SELECT bukrs
belnr
gjahr
blart
bldat
budat
cpudt
xblnr
bktxt
waers
kursf
hwaer FROM bkpf INTO TABLE it_bkpf
FOR ALL ENTRIES IN it_reqlog_adp
WHERE ( bukrs IN s_bukrs ) AND
( belnr = it_reqlog_adp-docnumber ) AND
( blart = 'I4' OR blart = 'I5' ) AND
( cpudt IN s_ldate ) .
SORT it_bkpf BY bukrs belnr cpudt.
ENDIF.
Kindly share your suggestions to resolve my issue.
Regards
Rajasekhar S
01-05-2015 9:58 AM
Hi Rajasekar,
BKPF is large table, so you have to put 1st key field (BUKRS) in where condition to remove code inspector error. similarly
ziftr_reqlog_adp is large table, so you have to put 1st key field (IND) in where condition to remove code inspector error. please refer the code below.
SELECT uniqid
ldate
legacy_id
docnumber FROM ziftr_reqlog_adp
INTO TABLE it_reqlog_adp
WHERE IND IN S_IND AND ldate IN s_ldate AND
docnumber NE ''.
Thanks,
Marimuthu.K
01-05-2015 9:59 AM
Have you checked that matching data actually exists in your production system? Have you run in debug and checked that it_reqlog_adp actually contains anything. Have you checked in debug the values of s_l_date )
General hint 1 - IF NOT it_reqlog_adp[] IS INITIAL. is old. Better to use IF it_reqlog_adp[] IS NOT INITIAL. as it is more naturally readable.
General hint 2 - don't use FOR ALL ENTRIES. Use an INNER JOIN. In most cases you will get far better performance. Also,INNER JOIN requires less code, is easier to understand and less prone to error.
01-05-2015 10:11 AM
01-05-2015 10:33 AM
Sorry Mathew, mea culpa! BKPF is transparent table, BSEG is Cluster.
So you are right. Regards, Clemens
01-05-2015 10:57 AM
Anyway, I'm on HANA. What are these "cluster tables" of which you speak?
01-05-2015 10:10 AM
Hi Rajasekhar S,
just some hints:
1. The code inspector errors are not related to the observed error. When accessing BKPF you must have the primary key fields BUKRS BELNR GJAHR. If you have nore than one BUKRS put the possible BUKRS values into a range of posiible values (IEQ), same applies for GJAHR. Then the system will make use of the index.
The docnumber NE '' in the where clause disables the use of an index (if there is one for docnumber). You may create an additional (non-unique) index for ldate.
2. If the report does not find any BKPF data for ziftr_reqlog_adp data, then you should manually find existing records. Frequent errors are wrog internal format like missing leading zeros for docnumber in one of the tables involved. Also make sure that date formatting is the same.
3. For access to BKPF it is always better to get the data from BKPF secondary index tables BSAD BSIS BSID BSAS whichever will apply for you. This will reduces database access times significantly.
Hope you get it work!
Best regards Clemens
01-05-2015 10:13 AM
01-05-2015 11:00 AM
Aravind I wrote:
What is the issue in production? Whether the program dumps.
Which part of the highlighted section of
Here in production system 1st select statement getting the data from Z table and 2nd select statement not getting any data from BKPF table as mention in where conditions.
did you fail to understand?
01-05-2015 10:36 AM
01-05-2015 12:27 PM
According to your screenshot, the data element for DOCNUMBER is VBELN_VF (billing document) with domain VBELN and VBUK as check table (sales documents).
Is it really correct to select BKPF (FI documents) with these values?
Thomas
01-05-2015 12:42 PM
Dear All,
Thanx for your quick responses,
As per above suggestions, i have been used first field in where condition in both select statements and Code inspector 2 error has been resolved as mention in below:
But still i am unable to run the report in production system.
1st select statement getting the data from Z table and 2nd select statement not getting any data from BKPF table as mention in where conditions.
SELECT ind
uniqid
ldate
legacy_id
docnumber FROM ziftr_reqlog_adp
INTO TABLE it_reqlog_adp
WHERE ind EQ 'DOCP' AND
ldate IN s_ldate AND
docnumber NE ''.
SORT it_reqlog_adp BY docnumber ldate.
IF NOT it_reqlog_adp[] IS INITIAL.
SELECT bukrs
belnr
gjahr
blart
bldat
budat
cpudt
xblnr
bktxt
waers
kursf
hwaer FROM bkpf INTO TABLE it_bkpf
FOR ALL ENTRIES IN it_reqlog_adp
WHERE ( bukrs IN s_bukrs ) AND
( belnr = it_reqlog_adp-docnumber ) AND
( blart = 'I4' OR blart = 'I5' ) AND
( cpudt IN s_ldate ) .
SORT it_bkpf BY bukrs belnr cpudt.
ENDIF.
Kindly share your suggestions to resolve my issue.
Regards
Rajasekhar S
01-05-2015 2:52 PM
There is a conversion exit (ALPHA) on BKPF-BELNR. So you will have to manually pad the document number from your custom table with leading zeroes or better still, add the same conversion exit to that field.
Rob
01-06-2015 9:56 AM
Dear All,
Thank you for all your valuable inputs, issue has been resolved.
Here document number is the issue, Once "CONVERSION_EXIT_ALPHA_INPUT" used for document number field.
In our case in 1st Ztable we are getting 9 digit doc number like "123456789", but in second table field
maintained in BKPF table like "0123456789". So here it is mismatching in where condition.
Regards
Rajasekhar S