Select data in SAP ABAP - Starting from dynamic row number
I successfully deal with DATA Selection from SAP Tables by using RFC Module. Issue I'm facing is to understand best practice of SELECTING data that will not always consider data starting from row 0.
Example: First RFC call will fetch 1000 records from KNA1. ( I will log in custom transparent table how many records in total where considered so far).
New RFC Call should take next 1000 rows but starting from row 1000 till 2000. Is there some elegant way of dealing with this situation?
Using of Cursor is not possible since there are 2 consecutive calls of same RFC.. cursor value will be reset.
Otherwise I should always selecting everything and distinguish requested data by looping the total data which will be a lot of time consume...
Thanks for any suggestions!
sap selection abap
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
add a comment |
I successfully deal with DATA Selection from SAP Tables by using RFC Module. Issue I'm facing is to understand best practice of SELECTING data that will not always consider data starting from row 0.
Example: First RFC call will fetch 1000 records from KNA1. ( I will log in custom transparent table how many records in total where considered so far).
New RFC Call should take next 1000 rows but starting from row 1000 till 2000. Is there some elegant way of dealing with this situation?
Using of Cursor is not possible since there are 2 consecutive calls of same RFC.. cursor value will be reset.
Otherwise I should always selecting everything and distinguish requested data by looping the total data which will be a lot of time consume...
Thanks for any suggestions!
sap selection abap
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
1
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53
add a comment |
I successfully deal with DATA Selection from SAP Tables by using RFC Module. Issue I'm facing is to understand best practice of SELECTING data that will not always consider data starting from row 0.
Example: First RFC call will fetch 1000 records from KNA1. ( I will log in custom transparent table how many records in total where considered so far).
New RFC Call should take next 1000 rows but starting from row 1000 till 2000. Is there some elegant way of dealing with this situation?
Using of Cursor is not possible since there are 2 consecutive calls of same RFC.. cursor value will be reset.
Otherwise I should always selecting everything and distinguish requested data by looping the total data which will be a lot of time consume...
Thanks for any suggestions!
sap selection abap
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
I successfully deal with DATA Selection from SAP Tables by using RFC Module. Issue I'm facing is to understand best practice of SELECTING data that will not always consider data starting from row 0.
Example: First RFC call will fetch 1000 records from KNA1. ( I will log in custom transparent table how many records in total where considered so far).
New RFC Call should take next 1000 rows but starting from row 1000 till 2000. Is there some elegant way of dealing with this situation?
Using of Cursor is not possible since there are 2 consecutive calls of same RFC.. cursor value will be reset.
Otherwise I should always selecting everything and distinguish requested data by looping the total data which will be a lot of time consume...
Thanks for any suggestions!
sap selection abap
sap selection abap
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
edited Nov 23 '18 at 12:46
Sandra Rossi
2,3791415
2,3791415
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
asked Nov 23 '18 at 11:02
OrdinaryPersonOrdinaryPerson
113
113
1
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53
add a comment |
1
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53
1
1
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53
add a comment |
1 Answer
1
active
oldest
votes
Use OFFSET
In the SELECT with OFFSET:
SELECT * kna1 UP TO 1000 ROWS
OFFSET (lv_offset)
WHERE ...
ORDER BY ...
If lv_offset contains 2000 for example, it will return the rows 2001-3000 by the ordering.
According to the online help, you have to use ORDER BY in the SELECT.
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445469%2fselect-data-in-sap-abap-starting-from-dynamic-row-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use OFFSET
In the SELECT with OFFSET:
SELECT * kna1 UP TO 1000 ROWS
OFFSET (lv_offset)
WHERE ...
ORDER BY ...
If lv_offset contains 2000 for example, it will return the rows 2001-3000 by the ordering.
According to the online help, you have to use ORDER BY in the SELECT.
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
add a comment |
Use OFFSET
In the SELECT with OFFSET:
SELECT * kna1 UP TO 1000 ROWS
OFFSET (lv_offset)
WHERE ...
ORDER BY ...
If lv_offset contains 2000 for example, it will return the rows 2001-3000 by the ordering.
According to the online help, you have to use ORDER BY in the SELECT.
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
add a comment |
Use OFFSET
In the SELECT with OFFSET:
SELECT * kna1 UP TO 1000 ROWS
OFFSET (lv_offset)
WHERE ...
ORDER BY ...
If lv_offset contains 2000 for example, it will return the rows 2001-3000 by the ordering.
According to the online help, you have to use ORDER BY in the SELECT.
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
Use OFFSET
In the SELECT with OFFSET:
SELECT * kna1 UP TO 1000 ROWS
OFFSET (lv_offset)
WHERE ...
ORDER BY ...
If lv_offset contains 2000 for example, it will return the rows 2001-3000 by the ordering.
According to the online help, you have to use ORDER BY in the SELECT.
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
edited Nov 26 '18 at 18:43
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
answered Nov 23 '18 at 12:08
AndrásAndrás
427516
427516
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
add a comment |
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
Hello Andras ! Thanks for your answer looks very convinient. But I struggle to understand to correct procession. What I have done is : REFRESH ET_RET_TABLE. SELECT (GV_SELECT_STATEMENT) FROM (IV_TABNAME) INTO TABLE ET_RET_TABLE UP TO GV_PACKAGE_SIZE ROWS ORDER BY (GV_PRIMARY_KEYS) ASCENDING OFFSET 10. At the end it takes first 10 records again. OFFSET should somehow define from which record to start if i'm not wrong, right ? Many thanks for helping.
– OrdinaryPerson
Nov 23 '18 at 13:35
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
@OrdinaryPerson, if the OFFSET value is hardcoded, it will always return the same lines, in your case lines 11-(10+gv_package_size)
– András
Nov 23 '18 at 23:25
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
Hi Andras.. My code implementing OFFSET looks like is not considered at all. Somewhere I read that OFFSET is not supported in Open SQL. Currently I'm on Oracle DB but Open SQL is independent of databases. I don't understand on which environment OFFSET is valid to process? Thank you !
– OrdinaryPerson
Nov 26 '18 at 13:13
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
@OrdinaryPerson You need to use order by apparently
– András
Nov 26 '18 at 18:53
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445469%2fselect-data-in-sap-abap-starting-from-dynamic-row-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If you leave the RFC connection alive and don't close the cursor, it remains opened for next calls...
– Sandra Rossi
Nov 23 '18 at 12:45
Hi Sandra. Unfortunately that RFC Connection will be closed and same one will be called all over. It's more like serialization process to retrieve data from SAP System in small packages. Each RFC will transport about 3000 data records. Now I struggle to find a way to do not take them always from beginning and looping in RFC before I get them back. :(
– OrdinaryPerson
Nov 23 '18 at 14:57
Did you make sure you have no possibility to let the RFC connection open? Otherwise, did you check whether ABAP Channels (AMC, APC) or ABAP Daemons (ADF) can be used? (I think they live as long as you wish and data is persisted in their own memory session) Unfortunately I never used them so I can't tell more about them, and I even don't know if it's really relevant. You need minimum ABAP >= 7.40 and >= 7.52 for ADF.
– Sandra Rossi
Nov 23 '18 at 15:53