How to set a variable from one condition and use in another in SQL
0
I have the following query:
DECLARE @someValue INT
SELECT *
FROM dummytable
WHERE EXISTS (SELECT @someValue = somecolumn
FROM dummytable2
WHERE tableindex = dummytableIndex)
AND EXISTS (SELECT *
FROM dummytable3
WHERE somecolumn = @someValue)
AND SomeDummyFunctionReturnsBool(@someValue) = 1
The error is that this statement is not allowed @someValue = somecolumn
I am unable to set the value for @someValue and use it later.
I tried replacing the error line as @someValue AS somecolumn but on printing @someValue, it reports NULL
Please share if this is supported and the solution to achieve the same.
sql
sql-server asked Jan 2 at 4:57
thepacethepace
1,640918
|
show 1 more comment
0
I have the following query:
DECLARE @someValue INT
SELECT *
FROM dummytable
WHERE EXISTS (SELECT @someValue = somecolumn
FROM dummytable2
WHERE tableindex = dummytableIndex)
AND EXISTS (SELECT *
FROM dummytable3
WHERE somecolumn = @someValue)
AND SomeDummyFunctionReturnsBool(@someValue) = 1
The error is that this statement is not allowed @someValue = somecolumn
I am unable to set the value for @someValue and use it later.
I tried replacing the error line as @someValue AS somecolumn but on printing @someValue, it reports NULL
Please share if this is supported and the solution to achieve the same.
sql
sql-server asked Jan 2 at 4:57
thepacethepace
1,640918
you should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
The expression(s) in the list of column of a correlated subquery toEXISTSaren't actually evaluated and even ifEXISTSactually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (asCREATEstatements), sample data (asINSERTstatements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.
– sticky bit
Jan 2 at 5:23
|
show 1 more comment
0
0
0
I have the following query:
DECLARE @someValue INT
SELECT *
FROM dummytable
WHERE EXISTS (SELECT @someValue = somecolumn
FROM dummytable2
WHERE tableindex = dummytableIndex)
AND EXISTS (SELECT *
FROM dummytable3
WHERE somecolumn = @someValue)
AND SomeDummyFunctionReturnsBool(@someValue) = 1
The error is that this statement is not allowed @someValue = somecolumn
I am unable to set the value for @someValue and use it later.
I tried replacing the error line as @someValue AS somecolumn but on printing @someValue, it reports NULL
Please share if this is supported and the solution to achieve the same.
sql
sql-server asked Jan 2 at 4:57
thepacethepace
1,640918
I have the following query:
DECLARE @someValue INT
SELECT *
FROM dummytable
WHERE EXISTS (SELECT @someValue = somecolumn
FROM dummytable2
WHERE tableindex = dummytableIndex)
AND EXISTS (SELECT *
FROM dummytable3
WHERE somecolumn = @someValue)
AND SomeDummyFunctionReturnsBool(@someValue) = 1
The error is that this statement is not allowed @someValue = somecolumn
I am unable to set the value for @someValue and use it later.
I tried replacing the error line as @someValue AS somecolumn but on printing @someValue, it reports NULL
Please share if this is supported and the solution to achieve the same.
sql
sql-server sql
sql-server asked Jan 2 at 4:57
thepacethepace
1,640918
asked Jan 2 at 4:57
thepacethepace
1,640918
edited Jan 2 at 8:24
thepace
asked Jan 2 at 4:57
thepacethepace
1,640918
asked Jan 2 at 4:57
thepacethepace
1,640918
asked Jan 2 at 4:57
thepacethepace
1,640918
1,640918
you should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
The expression(s) in the list of column of a correlated subquery toEXISTSaren't actually evaluated and even ifEXISTSactually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (asCREATEstatements), sample data (asINSERTstatements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.
– sticky bit
Jan 2 at 5:23
|
show 1 more comment
you should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
The expression(s) in the list of column of a correlated subquery toEXISTSaren't actually evaluated and even ifEXISTSactually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (asCREATEstatements), sample data (asINSERTstatements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.
– sticky bit
Jan 2 at 5:23
you should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
you should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
The expression(s) in the list of column of a correlated subquery to
EXISTS aren't actually evaluated and even if EXISTS actually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (as CREATE statements), sample data (as INSERT statements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.– sticky bit
Jan 2 at 5:23
The expression(s) in the list of column of a correlated subquery to
EXISTS aren't actually evaluated and even if EXISTS actually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (as CREATE statements), sample data (as INSERT statements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.– sticky bit
Jan 2 at 5:23
|
show 1 more comment
2 Answers
2
active
oldest
votes
1
There is an option to NOT use the variable and rewrite the query as follows for example
select *
from dummytable
where exists (select 1
from dummytable3 t3
join dummytable2 t2
on t3.somecolumn=t2.somecolumn
where t2.uniqueindex= dummytable.tableindex
)
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
add a comment |
0
I tried this for now as a solution. More on lines with George's answer
DECLARE @someValue INT
select * from dummytable
where exists
(select somecolumn
from dummytable2 dt2
where tableindex=dummytableIndex
and exists (select * from dummytable3 where somecolumn = dt2.somecolumn
and SomeDummyFunctionReturnsBool(@someValue) = 1
)
answered Jan 2 at 5:39
thepacethepace
1,640918
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
});
}
});
draft saved
draft discarded
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%2f54001398%2fhow-to-set-a-variable-from-one-condition-and-use-in-another-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
There is an option to NOT use the variable and rewrite the query as follows for example
select *
from dummytable
where exists (select 1
from dummytable3 t3
join dummytable2 t2
on t3.somecolumn=t2.somecolumn
where t2.uniqueindex= dummytable.tableindex
)
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
add a comment |
1
There is an option to NOT use the variable and rewrite the query as follows for example
select *
from dummytable
where exists (select 1
from dummytable3 t3
join dummytable2 t2
on t3.somecolumn=t2.somecolumn
where t2.uniqueindex= dummytable.tableindex
)
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
add a comment |
1
1
1
There is an option to NOT use the variable and rewrite the query as follows for example
select *
from dummytable
where exists (select 1
from dummytable3 t3
join dummytable2 t2
on t3.somecolumn=t2.somecolumn
where t2.uniqueindex= dummytable.tableindex
)
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
There is an option to NOT use the variable and rewrite the query as follows for example
select *
from dummytable
where exists (select 1
from dummytable3 t3
join dummytable2 t2
on t3.somecolumn=t2.somecolumn
where t2.uniqueindex= dummytable.tableindex
)
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
answered Jan 2 at 5:03
George JosephGeorge Joseph
1,59059
1,59059
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
add a comment |
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
I have updated my question for better understanding of the issue.
– thepace
Jan 2 at 5:06
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@thepace . . . Changing a question after it has been answered is rude -- if it invalidates the answer -- because such changes can attract downvotes. This addresses your fundamental question.
– Gordon Linoff
Jan 2 at 12:44
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
@GordonLinoff: Apologies if that's the case. I wasn't able to explain the exact issue in the first draft so I ensured to update it with the actual requirement I have. Also, I have given due credit to George in the answer I posted.
– thepace
Jan 3 at 4:08
add a comment |
0
I tried this for now as a solution. More on lines with George's answer
DECLARE @someValue INT
select * from dummytable
where exists
(select somecolumn
from dummytable2 dt2
where tableindex=dummytableIndex
and exists (select * from dummytable3 where somecolumn = dt2.somecolumn
and SomeDummyFunctionReturnsBool(@someValue) = 1
)
answered Jan 2 at 5:39
thepacethepace
1,640918
add a comment |
0
I tried this for now as a solution. More on lines with George's answer
DECLARE @someValue INT
select * from dummytable
where exists
(select somecolumn
from dummytable2 dt2
where tableindex=dummytableIndex
and exists (select * from dummytable3 where somecolumn = dt2.somecolumn
and SomeDummyFunctionReturnsBool(@someValue) = 1
)
answered Jan 2 at 5:39
thepacethepace
1,640918
add a comment |
0
0
0
I tried this for now as a solution. More on lines with George's answer
DECLARE @someValue INT
select * from dummytable
where exists
(select somecolumn
from dummytable2 dt2
where tableindex=dummytableIndex
and exists (select * from dummytable3 where somecolumn = dt2.somecolumn
and SomeDummyFunctionReturnsBool(@someValue) = 1
)
answered Jan 2 at 5:39
thepacethepace
1,640918
I tried this for now as a solution. More on lines with George's answer
DECLARE @someValue INT
select * from dummytable
where exists
(select somecolumn
from dummytable2 dt2
where tableindex=dummytableIndex
and exists (select * from dummytable3 where somecolumn = dt2.somecolumn
and SomeDummyFunctionReturnsBool(@someValue) = 1
)
answered Jan 2 at 5:39
thepacethepace
1,640918
answered Jan 2 at 5:39
thepacethepace
1,640918
answered Jan 2 at 5:39
thepacethepace
1,640918
answered Jan 2 at 5:39
thepacethepace
1,640918
1,640918
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54001398%2fhow-to-set-a-variable-from-one-condition-and-use-in-another-in-sql%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 should first declare the variable
– Ajan Balakumaran
Jan 2 at 4:59
what is the error though
– Himanshu Ahuja
Jan 2 at 5:00
Completed the code and added more details.
– thepace
Jan 2 at 5:06
Use a cursor and loop to get the value for each row in your dummytable.
– Vlam
Jan 2 at 5:11
The expression(s) in the list of column of a correlated subquery to
EXISTSaren't actually evaluated and even ifEXISTSactually retrieved the result of the subquery, that isn't necessarily limited to one row. So your query doesn't make sense at all. To get further advise, edit your question an add the tables' and function's definition (asCREATEstatements), sample data (asINSERTstatements) and expected result with that sample data. Also comprehensively explain the logical relation between the input and the output.– sticky bit
Jan 2 at 5:23