Oracle query to pick the proper record with Join and Inner query
I need a condition to write the query in oracle to satisfy below condition
A and B are 2 tables
- A will have copy of B not true all the case
- In A I will have 2 records if override is 'Y' . one is for override as 'N' and override as 'Y' and onevale can be same or not
- For B we can have the same as 'Y' or 'N' for override but I need the latest can retrieve using indx where it is a primary key(latest
pk) - In A override can be 'Y' then it will check with latest override of B to find there is a change on onevale
- If A override can be 'N' but it should compare the latest override of 'Y' in 'B' to find there is a change on onevale
- If onevale is same and if override is same then no record should display
select *
From A A1, B B1
Where A1.SS = B1.SS
And A1.TS = B1.TS
and a1.indx = (select max(indx)
from A
where tc = 'T1234'
and sc = 'S1234'
)
and A1.override = B1.override
and (a1.onevale <> b1.onevale)
Can some one explain this query will satisfy all the condition
sql oracle oracle11g oracle10g
add a comment |
I need a condition to write the query in oracle to satisfy below condition
A and B are 2 tables
- A will have copy of B not true all the case
- In A I will have 2 records if override is 'Y' . one is for override as 'N' and override as 'Y' and onevale can be same or not
- For B we can have the same as 'Y' or 'N' for override but I need the latest can retrieve using indx where it is a primary key(latest
pk) - In A override can be 'Y' then it will check with latest override of B to find there is a change on onevale
- If A override can be 'N' but it should compare the latest override of 'Y' in 'B' to find there is a change on onevale
- If onevale is same and if override is same then no record should display
select *
From A A1, B B1
Where A1.SS = B1.SS
And A1.TS = B1.TS
and a1.indx = (select max(indx)
from A
where tc = 'T1234'
and sc = 'S1234'
)
and A1.override = B1.override
and (a1.onevale <> b1.onevale)
Can some one explain this query will satisfy all the condition
sql oracle oracle11g oracle10g
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26
add a comment |
I need a condition to write the query in oracle to satisfy below condition
A and B are 2 tables
- A will have copy of B not true all the case
- In A I will have 2 records if override is 'Y' . one is for override as 'N' and override as 'Y' and onevale can be same or not
- For B we can have the same as 'Y' or 'N' for override but I need the latest can retrieve using indx where it is a primary key(latest
pk) - In A override can be 'Y' then it will check with latest override of B to find there is a change on onevale
- If A override can be 'N' but it should compare the latest override of 'Y' in 'B' to find there is a change on onevale
- If onevale is same and if override is same then no record should display
select *
From A A1, B B1
Where A1.SS = B1.SS
And A1.TS = B1.TS
and a1.indx = (select max(indx)
from A
where tc = 'T1234'
and sc = 'S1234'
)
and A1.override = B1.override
and (a1.onevale <> b1.onevale)
Can some one explain this query will satisfy all the condition
sql oracle oracle11g oracle10g
I need a condition to write the query in oracle to satisfy below condition
A and B are 2 tables
- A will have copy of B not true all the case
- In A I will have 2 records if override is 'Y' . one is for override as 'N' and override as 'Y' and onevale can be same or not
- For B we can have the same as 'Y' or 'N' for override but I need the latest can retrieve using indx where it is a primary key(latest
pk) - In A override can be 'Y' then it will check with latest override of B to find there is a change on onevale
- If A override can be 'N' but it should compare the latest override of 'Y' in 'B' to find there is a change on onevale
- If onevale is same and if override is same then no record should display
select *
From A A1, B B1
Where A1.SS = B1.SS
And A1.TS = B1.TS
and a1.indx = (select max(indx)
from A
where tc = 'T1234'
and sc = 'S1234'
)
and A1.override = B1.override
and (a1.onevale <> b1.onevale)
Can some one explain this query will satisfy all the condition
sql oracle oracle11g oracle10g
sql oracle oracle11g oracle10g
edited Nov 23 '18 at 19:22
Littlefoot
20.5k71433
20.5k71433
asked Nov 23 '18 at 4:22
user3428736
356618
356618
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26
add a comment |
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26
add a comment |
0
active
oldest
votes
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%2f53440624%2foracle-query-to-pick-the-proper-record-with-join-and-inner-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53440624%2foracle-query-to-pick-the-proper-record-with-join-and-inner-query%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
You have data, we don't. So, what happens when you run the query? Does it return a desired result? If so, then it works. If not, you'll have to fix it. You mentioned some "Y" and "N", but I don't see any in code you posted. I presume that test case might help the community help you. Consider posting it.
– Littlefoot
Nov 23 '18 at 19:26