0

In the below query, the columns GLName and GLDesc fetch the same value, but I need to have the same value with 2 different column names to satisfy the requirement of a predefined Excel template.

SELECT T0.GLCode, (SELECT ST0.Name From GL ST0 WHERE ST0.Code = T0.GLCode) AS GLName, 
(SELECT ST0.Name FROM GL ST0 WHERE ST0.Code = T0.GLCode) AS GLDesc
FROM Trans T0

How can I reuse the result of the 1st subquery to avoid writing the 2nd subquery? This is a simplified example. In the real query, around 10 subqueries need to be reused in another subquery. In my case, it is difficult to build another table with all the required values from which to fetch the values in main query, as the master tables are huge and the values to be fetched from the master tables are only for specific codes which are based on the filtered records from the Trans table.

EDIT 1:

I am specifically asking about reuse of subquery. I do not want to use JOINs here because the real query with several JOINs takes around 60 seconds, whereas the query with subquery takes about 7-8 seconds. The only inconvenience is that I have to write the same subquery multiple times.

EDIT 2:

The query is inside a Stored Procedure, like this:

CREATE PROCEDURE [dbo].[My_Query_Proc]
AS
BEGIN

    SELECT T0.GLCode, (SELECT ST0.Name From GL ST0 WHERE ST0.Code = T0.GLCode) AS GLName, 
    (SELECT ST0.Name FROM GL ST0 WHERE ST0.Code = T0.GLCode) AS GLDesc
    FROM Trans T0

END

I prefer to use a single query in the Stored Procedure. I call this Stored Procedure from an Excel File in External Data command. I prefer to encapsulate it in Stored Procedure, instead of writing the query directly in Excel file.

If I try adding a variable:

DECLARE @GLName NVARCHAR(255)

and changing the query to:

    SELECT T0.GLCode, @GLName = (SELECT ST0.Name From GL ST0 WHERE ST0.Code = T0.GLCode) AS GLName, 
    (SELECT ST0.Name FROM GL ST0 WHERE ST0.Code = T0.GLCode) AS GLDesc
    FROM Trans T0

I get syntax error. Is there a way without using cursors or temp tables or writing procedural logic to fetch the result of 1st subquery into a variable, and use that variable's value for the column GLName as well as GLDesc?

My desired 'imaginary' query inside the SP is like this:

CREATE PROCEDURE [dbo].[My_Query_Proc]
AS
BEGIN

    DECLARE @GLName NVARCHAR(255)

    SELECT T0.GLCode, @GLName = (SELECT ST0.Name From GL ST0 WHERE ST0.Code = T0.GLCode) AS GLName, 
    @GLName AS GLDesc
    FROM Trans T0

END

Is there any real query syntax corresponding to my imaginary query above?

AllSolutions
  • 1,176
  • 5
  • 19
  • 40
  • One way to reuse a sub-query is with the `cross apply` operator. But given all your comments below changing the nature of your question, I don't know if it would work for you or not. – Dale K May 24 '22 at 07:19
  • "I do not want to use JOINs here because the real query with several JOINs takes around 60 seconds, whereas the query with subquery takes about 7-8 seconds." most likely there is some kind of statistics or parameter sniffing issue. I can't imagine that two subqueries *should* be quicker than a single join. Please share the query plans of both via https://brentozar.com/pastetheplan – Charlieface May 24 '22 at 08:50

2 Answers2

1

Putting your subquery in an apply makes it available for reuse:

select t.GLCode, g.GLName, g.GLName GLDesc
from Trans t
outer apply (
  select [Name] GLName
  from GL
  where GL.Code = t.GLCode
)g;
Stu
  • 30,392
  • 6
  • 14
  • 33
  • This is almost similar to a JOIN. I want to know whether it is possible to re-use a subquery result in another column in main query. The only additional information is that this query is the only query in a Stored Procedure, and I do not want to write extensive procedural logic, but am ok to use variables if there is a mechanism to do that in a single query – AllSolutions May 24 '22 at 14:43
0

Just use proper join then:

SELECT T0.GLCode, ST0.Name AS GLName, ST0.Name AS GLDesc
FROM Trans T0
    INNER JOIN GL ST0 ON T0.GLCode = ST0.Code
Hana
  • 824
  • 4
  • 14
  • 30
  • In my actual query, since the JOIN involves several tables, the query with JOIN is taking around 60 seconds, whereas the query with subquery is taking around 7-8 seconds. Hence, I prefer to use subquery. Just that I wanted to know whether it is technically possible to reuse subquery by storing the result in some variable or any other way? – AllSolutions May 24 '22 at 06:34
  • How's about using CTE? – Hana May 24 '22 at 06:38
  • My actual query will not only fetch data from several tables, but also from 5 different databases using the [DBNAME].[dbo].[table] syntax, and all such queries will be joined using UNION ALL. If I try using CTE, I have to write several CTEs. In that case, the 1st CTE will fetch the required codes, the 2nd CTE will fetch the names from master tables based on codes of the 1st CTE, and then I will need one more query to combine the 2 CTEs.. Although it can be done, I want to know whether it is possible to reuse subquery or not? – AllSolutions May 24 '22 at 06:42
  • So, is something like SELECT @ variable = (subquery) AS Col1, @variable AS Col2 possible? – AllSolutions May 24 '22 at 06:45
  • I don't think it's possible to do so. There is a similar question I found, CTE and JOIN were also suggested. https://stackoverflow.com/questions/2686919/is-possible-to-reuse-subqueries – Hana May 24 '22 at 06:46
  • If you still want to do so, consider to use T-SQL or Stored Procedure instead – Hana May 24 '22 at 06:53
  • ok, this query is inside a Stored Procedure. But when I try to use @variable = (subquery) or (SELECT colName INTO @ var FROM ...), I get syntax error. I do not want the stored procedure to have too much procedural logic, but I am okay to declare the variables and use them. I would prefer single query in the SP. – AllSolutions May 24 '22 at 07:01
  • 1
    You need to ensure your question contains a [mre] - you have oversimplified it and hence are having to explain yourself in comments. I suggest updating your question to make it clear the situation you are actually dealing with – Dale K May 24 '22 at 07:18