A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

Transact-SQL
1

Is there a way to make this work?

declare @myvar nvarchar(30);
select 
	P.ID,
	@myvar = (select top 1 T.ID from TRACE T where PART_ID = P.ID),
	@myvar as TRACE_ID,
	(select top 1 TIT.TRANSACTION_DATE from TRACE_INV_TRANS TIT where TIT.TRACE_ID = @myvar) as FIRST_TRANS_DATE
from 
	PART P

The real query is a few hundred lines long and includes several subqueries. In this example, I have created a mini-subquery for the @myvar assignment; the real one is quite a bit longer. The goal is to be able to save the results of [(select top 1 T.ID from TRACE T where PART_ID = P.ID)] into a variable that I can then use farther down in the WHERE clause of another subquery.

2

You can do something like this:

DECLARE @myvar nvarchar(30);
SET @myvar = (select top 1 T.ID from TRACE T where PART_ID = P.ID);

SELECT
         P.ID,
	@myvar as TRACE_ID,
	(select top 1 TIT.TRANSACTION_DATE from TRACE_INV_TRANS TIT where TIT.TRACE_ID = @myvar) as FIRST_TRANS_DATE
from 
	PART P
3

So, @myvar holds not a value but a query which results in a value?

I like it! Thanks!

4

Actually - no, @myvar holds the results of the query - not the query itself. Another way to do this - without using variables:

SELECT p.ID
     , tr.TRACE_ID
     , td.FIRST_TRANS_DATE
  FROM part    p
 CROSS APPLY (SELECT TOP (1)
                     TRACE_ID = t.ID
                FROM TRACE    t
               WHERE t.PART_ID = p.ID
               ORDER BY ... need something here, top without order will be random
             )                     tr
 CROSS APPLY (SELECT TOP (1)
                     FIRST_TRANS_DATE = tit.TRANSACTION_DATE
                FROM TRACE_INV_TRANS      tit
               WHERE tit.TRACE_ID = tr.TRACE_ID
               ORDER BY ... need something here, top without order will be random
             )                     td

Using CROSS APPLY - we get the trace id associated with the part id for each row. The second cross apply can then use the results from the first cross apply to get the associated transaction date - and the outer select just references the columns returned.

Note: TOP without an ORDER BY allows SQL Server to return any matching row, depending on how SQL Server decides to get the data. Since you probably want the latest trace - then ORDER BY t.ID DESC - and the first transaction date then ORDER BY tit.TRANSACTION_DATE ASC.