Reuse result from subquery in a function Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!Insert into … values ( SELECT … FROM … )How to return only the Date from a SQL Server DateTime datatypeHow to concatenate text from multiple rows into a single text string in SQL server?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?reuse computated data from a subqueryGetting Conversion failed error in stored procedureAggregate function at Subquery And Performance IssueSubquery resulting duplicatesUsing min with cast nvarchar to float with group by throws error in converting
Random body shuffle every night—can we still function?
Why weren't discrete x86 CPUs ever used in game hardware?
What is an "asse" in Elizabethan English?
What do you call the main part of a joke?
Strange behavior of Object.defineProperty() in JavaScript
Most bit efficient text communication method?
Why is it faster to reheat something than it is to cook it?
How do I find out the mythology and history of my Fortress?
Is there hard evidence that the grant peer review system performs significantly better than random?
How could we fake a moon landing now?
Semigroups with no morphisms between them
Is multiple magic items in one inherently imbalanced?
One-one communication
Do wooden building fires get hotter than 600°C?
The test team as an enemy of development? And how can this be avoided?
Why are vacuum tubes still used in amateur radios?
Do I really need to have a message in a novel to appeal to readers?
Does the Mueller report show a conspiracy between Russia and the Trump Campaign?
Why are my pictures showing a dark band on one edge?
Why does it sometimes sound good to play a grace note as a lead in to a note in a melody?
Karn the great creator - 'card from outside the game' in sealed
Maximum summed subsequences with non-adjacent items
Putting class ranking in CV, but against dept guidelines
How often does castling occur in grandmaster games?
Reuse result from subquery in a function
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Data science time! April 2019 and salary with experience
The Ask Question Wizard is Live!Insert into … values ( SELECT … FROM … )How to return only the Date from a SQL Server DateTime datatypeHow to concatenate text from multiple rows into a single text string in SQL server?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?reuse computated data from a subqueryGetting Conversion failed error in stored procedureAggregate function at Subquery And Performance IssueSubquery resulting duplicatesUsing min with cast nvarchar to float with group by throws error in converting
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a query with two subqueries that basically do the same thing. I'd like to only perform the subquery once (within the STUFF function) and reuse the results instead of performing it a second time.
SELECT distinct MIN(alert.alert_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as alertId,
CAST(CONVERT(CHAR(17), alert.effective_date, 113) as datetime) as effectiveDate,
STUFF((
SELECT ',' + alert_2.description
FROM alert_table alert_2
INNER JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as alertDescription,
MIN(std_alert_type_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as stdAlertTypeId,
STUFF((
SELECT ',' + CONVERT(varchar(10), alert_2.alert_id)
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id
AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
FROM alert_table alert
Is it possible to perform a query like
SELECT alert_2.description, alert_2.alert_id
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
and just reuse the results from that query in the call to STUFF. Something like
STUFF((
SELECT ',' + other_result.alert_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
sql subquery aggregate-functions
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
add a comment |
I have a query with two subqueries that basically do the same thing. I'd like to only perform the subquery once (within the STUFF function) and reuse the results instead of performing it a second time.
SELECT distinct MIN(alert.alert_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as alertId,
CAST(CONVERT(CHAR(17), alert.effective_date, 113) as datetime) as effectiveDate,
STUFF((
SELECT ',' + alert_2.description
FROM alert_table alert_2
INNER JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as alertDescription,
MIN(std_alert_type_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as stdAlertTypeId,
STUFF((
SELECT ',' + CONVERT(varchar(10), alert_2.alert_id)
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id
AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
FROM alert_table alert
Is it possible to perform a query like
SELECT alert_2.description, alert_2.alert_id
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
and just reuse the results from that query in the call to STUFF. Something like
STUFF((
SELECT ',' + other_result.alert_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
sql subquery aggregate-functions
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
add a comment |
I have a query with two subqueries that basically do the same thing. I'd like to only perform the subquery once (within the STUFF function) and reuse the results instead of performing it a second time.
SELECT distinct MIN(alert.alert_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as alertId,
CAST(CONVERT(CHAR(17), alert.effective_date, 113) as datetime) as effectiveDate,
STUFF((
SELECT ',' + alert_2.description
FROM alert_table alert_2
INNER JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as alertDescription,
MIN(std_alert_type_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as stdAlertTypeId,
STUFF((
SELECT ',' + CONVERT(varchar(10), alert_2.alert_id)
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id
AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
FROM alert_table alert
Is it possible to perform a query like
SELECT alert_2.description, alert_2.alert_id
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
and just reuse the results from that query in the call to STUFF. Something like
STUFF((
SELECT ',' + other_result.alert_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
sql subquery aggregate-functions
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
I have a query with two subqueries that basically do the same thing. I'd like to only perform the subquery once (within the STUFF function) and reuse the results instead of performing it a second time.
SELECT distinct MIN(alert.alert_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as alertId,
CAST(CONVERT(CHAR(17), alert.effective_date, 113) as datetime) as effectiveDate,
STUFF((
SELECT ',' + alert_2.description
FROM alert_table alert_2
INNER JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as alertDescription,
MIN(std_alert_type_id) OVER (PARTITION BY CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)) as stdAlertTypeId,
STUFF((
SELECT ',' + CONVERT(varchar(10), alert_2.alert_id)
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id
AND (std_alert_2.std_alert_type_id = 2)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
FROM alert_table alert
Is it possible to perform a query like
SELECT alert_2.description, alert_2.alert_id
FROM alert_table alert_2
JOIN std_alert std_alert_2 on (alert_2.std_alert_id = std_alert_2.std_alert_id)
WHERE CAST(CONVERT(CHAR(17), alert_2.effective_date,113) as datetime) = CAST(CONVERT(CHAR(17), alert.effective_date,113) as datetime)
AND alert_2.client_id = alert.client_id AND (std_alert_2.std_alert_type_id = 2)
and just reuse the results from that query in the call to STUFF. Something like
STUFF((
SELECT ',' + other_result.alert_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as groupedAlertIds
sql subquery aggregate-functions
sql subquery aggregate-functions
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
asked Mar 8 at 22:05
ArchmedeArchmede
7481825
7481825
add a comment |
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%2f55071641%2freuse-result-from-subquery-in-a-function%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.
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%2f55071641%2freuse-result-from-subquery-in-a-function%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
