Insert data into oracle table with loop?FOR loop oracle query with calulationImporting data into Oracle...
Why is Shelob considered evil?
What does @ mean in a hostname in DNS configuration?
What is formjacking?
Can you say "leftside right"?
What is the reward?
How to transport 10,000 terrestrial trolls across ocean fast?
Why does this quiz question say that protons and electrons do not combine to form neutrons?
Including proofs of known theorems in master's thesis
How to run unit tests?
Sed-Grep-Awk operations
Is there a name for this series?
How do I avoid the "chosen hero" feeling?
bash aliases do not expand even with shopt expand_aliases
How bad is a Computer Science course that doesn't teach Design Patterns?
How do I make my single-minded character more interested in the main story?
How can I handle players killing my NPC outside of combat?
What is an efficient way to digitize a family photo collection?
Coworker asking me to not bring cakes due to self control issue. What should I do?
How to make transparent background from pdf to png
What does it mean for south of due west?
Python to write multiple dataframes and highlight rows inside an excel file
Is the percentage symbol a constant?
How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?
Does the double-bladed scimitar's special attack let you use your ability modifier for the damage of the attack?
Insert data into oracle table with loop?
FOR loop oracle query with calulationImporting data into Oracle 12Strategies to import big data into OracleLoad file based on line number in sqlldrLoad data from flat file into oracle tableOracle “insert /*+APPEND*/” into empty tableInsert value into table without knowin column nameHow to pass data and loop in procedure OracleHow to use multiple conditions in sql loader?ODI: Load data from XML file insert into Oracle Database
How to use sql to insert line detail data into header with the output like below.
line detail
MO_ID SEQ_NO QTY LEVEL
121M 2 3
121M 3 9
125M 2 6
125M 3 7
125M 4 1
Header
MO_ID LEVEL SEQ_NO QTY SERIAL_NO
121M T 1 101
121M T 1 102
125M T 1 201
125M T 1 202
125M T 1 203
Insert line detail into header so output will be:
MO_ID LEVEL SEQ_NO QTY SERIAL_NO Counter Seq
121M T 1 101 001 1
121M 2 3 001 2
121M 3 9 001 3
121M T 1 102 002 1
121M 2 3 002 2
121M 3 9 002 3
125M T 1 201 003 1
125M 2 6 003 2
125M 3 7 003 3
125M 4 1 003 4
125M T 1 202 004 1
125M 2 6 004 2
125M 3 7 004 3
125M 4 1 004 4
... and so on until the last serial is reached in header
oracle
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
How to use sql to insert line detail data into header with the output like below.
line detail
MO_ID SEQ_NO QTY LEVEL
121M 2 3
121M 3 9
125M 2 6
125M 3 7
125M 4 1
Header
MO_ID LEVEL SEQ_NO QTY SERIAL_NO
121M T 1 101
121M T 1 102
125M T 1 201
125M T 1 202
125M T 1 203
Insert line detail into header so output will be:
MO_ID LEVEL SEQ_NO QTY SERIAL_NO Counter Seq
121M T 1 101 001 1
121M 2 3 001 2
121M 3 9 001 3
121M T 1 102 002 1
121M 2 3 002 2
121M 3 9 002 3
125M T 1 201 003 1
125M 2 6 003 2
125M 3 7 003 3
125M 4 1 003 4
125M T 1 202 004 1
125M 2 6 004 2
125M 3 7 004 3
125M 4 1 004 4
... and so on until the last serial is reached in header
oracle
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
How to use sql to insert line detail data into header with the output like below.
line detail
MO_ID SEQ_NO QTY LEVEL
121M 2 3
121M 3 9
125M 2 6
125M 3 7
125M 4 1
Header
MO_ID LEVEL SEQ_NO QTY SERIAL_NO
121M T 1 101
121M T 1 102
125M T 1 201
125M T 1 202
125M T 1 203
Insert line detail into header so output will be:
MO_ID LEVEL SEQ_NO QTY SERIAL_NO Counter Seq
121M T 1 101 001 1
121M 2 3 001 2
121M 3 9 001 3
121M T 1 102 002 1
121M 2 3 002 2
121M 3 9 002 3
125M T 1 201 003 1
125M 2 6 003 2
125M 3 7 003 3
125M 4 1 003 4
125M T 1 202 004 1
125M 2 6 004 2
125M 3 7 004 3
125M 4 1 004 4
... and so on until the last serial is reached in header
oracle
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
How to use sql to insert line detail data into header with the output like below.
line detail
MO_ID SEQ_NO QTY LEVEL
121M 2 3
121M 3 9
125M 2 6
125M 3 7
125M 4 1
Header
MO_ID LEVEL SEQ_NO QTY SERIAL_NO
121M T 1 101
121M T 1 102
125M T 1 201
125M T 1 202
125M T 1 203
Insert line detail into header so output will be:
MO_ID LEVEL SEQ_NO QTY SERIAL_NO Counter Seq
121M T 1 101 001 1
121M 2 3 001 2
121M 3 9 001 3
121M T 1 102 002 1
121M 2 3 002 2
121M 3 9 002 3
125M T 1 201 003 1
125M 2 6 003 2
125M 3 7 003 3
125M 4 1 003 4
125M T 1 202 004 1
125M 2 6 004 2
125M 3 7 004 3
125M 4 1 004 4
... and so on until the last serial is reached in header
oracle
oracle
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
edited Jun 2 '16 at 6:16
a_horse_with_no_name
40.1k776112
40.1k776112
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
asked Jun 2 '16 at 4:02
AiLan TAiLan T
84
84
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This is only judging from the very little information you provide:
SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
FROM "Header" h
) h
JOIN (
SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
FROM "line detail"
UNION
SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
FROM "Header"
) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f140189%2finsert-data-into-oracle-table-with-loop%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
This is only judging from the very little information you provide:
SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
FROM "Header" h
) h
JOIN (
SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
FROM "line detail"
UNION
SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
FROM "Header"
) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
add a comment |
This is only judging from the very little information you provide:
SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
FROM "Header" h
) h
JOIN (
SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
FROM "line detail"
UNION
SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
FROM "Header"
) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
add a comment |
This is only judging from the very little information you provide:
SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
FROM "Header" h
) h
JOIN (
SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
FROM "line detail"
UNION
SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
FROM "Header"
) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
This is only judging from the very little information you provide:
SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
FROM "Header" h
) h
JOIN (
SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
FROM "line detail"
UNION
SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
FROM "Header"
) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
answered Jun 2 '16 at 7:57
Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister
4,2471819
4,2471819
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f140189%2finsert-data-into-oracle-table-with-loop%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
