SQL, use the result of main query into a sub query

Question

I am trying to add a column into my view. This column uses the previous result to calculate the new value.

However, it seems that it is not possible to use the result of the main query into the subquery.

To simplify the problem, it would give :

   SELECT DISTINCT Operation, Invoice, Corrective, 
   TestNumber = ( SELECT DISTINCT Operation, Invoice 
                   FROM Tab1
                   WHERE Operation = Tab1.Corrective)
   FROM Tab1

However, it does not work.

If I wrote the following, it works:

    SELECT DISTINCT Operation, Invoice, Corrective, 
    TestNumber = ( SELECT DISTINCT Operation, Invoice 
                   FROM Tab1
                   WHERE Operation = 20483)
    FROM Tab1

It seems that SQL tries to extract all number where we have the condition Operation = Corrective. WhereAs what I actually want is to take the previous Tab corrective result and inject it into my subquery. How should I do?

This is the real code :

SELECT DISTINCT dbo.Operation.Id AS OperationID, 
                dbo.Invoice.Id AS maxInvoice, 
                dbo.Operation.CorectiveId as Corective,  
                (
                    SELECT Distinct dbo.Invoice.InvoiceNumber
                    FROM dbo.Invoice INNER JOIN
                    dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
                    INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id =  dbo.InvoiceLine.InvoiceId 
                    INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                    INNER JOIN (
                        SELECT dbo.Operation.Id as OperationID, MAX(DISTINCT dbo.Invoice.Id) as MaxInvoice
                        FROM dbo.Invoice 
                        INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
                        INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
                        INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                    GROUP BY dbo.Operation.Id) sub1 ON dbo.Invoice.Id = sub1.MaxInvoice 
                                                   AND dbo.Operation.Id = sub1.OperationID

                    WHERE dbo.Operation.Id = dbo.Operation.CorectiveId
                ) as testnumber 
FROM (dbo.Invoice 
INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
INNER JOIN dbo.InvoiceLineHistory ON dbo.InvoiceLine.Id = dbo.InvoiceLineHistory.InvoiceLineId 
INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
INNER JOIN (
    SELECT dbo.Operation.Id as OperationID, MAX(DISTINCT dbo.Invoice.Id) as MaxInvoice
    FROM dbo.Invoice 
    INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
    INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
    INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
    GROUP BY dbo.Operation.Id) sub ON dbo.Invoice.Id = sub.MaxInvoice 
                                  AND dbo.Operation.Id = sub.OperationID 
)
ORDER BY dbo.Operation.Id 

Answer 1

Try the following code:

  SELECT DISTINCT dbo.Operation.Id AS OperationID,
              dbo.Invoice.Id AS maxInvoice,
              dbo.Operation.CorectiveId AS Corective,
              (SELECT DISTINCT dbo.Invoice.InvoiceNumber
                 FROM dbo.Invoice
                      INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                      INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                      INNER JOIN dbo.Operation operation_inner ON dbo.Invoice.OperationId = operation_inner.Id
                      INNER JOIN (  SELECT dbo.Operation.Id AS OperationID, MAX (DISTINCT dbo.Invoice.Id) AS MaxInvoice
                                      FROM dbo.Invoice
                                           INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                                           INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                                           INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                                  GROUP BY dbo.Operation.Id) sub1
                          ON dbo.Invoice.Id = sub1.MaxInvoice AND dbo.Operation.Id = sub1.OperationID
                WHERE dbo.Operation.Id = operation_inner.CorectiveId)
                  AS testnumber
FROM (dbo.Invoice
      INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
      INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
      INNER JOIN dbo.InvoiceLineHistory ON dbo.InvoiceLine.Id = dbo.InvoiceLineHistory.InvoiceLineId
      INNER JOIN dbo.Operation operation_outer ON dbo.Invoice.OperationId = operation_outer.Id
      INNER JOIN (  SELECT dbo.Operation.Id AS OperationID, MAX (DISTINCT dbo.Invoice.Id) AS MaxInvoice
                      FROM dbo.Invoice
                           INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                           INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                           INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                  GROUP BY dbo.Operation.Id) sub
          ON dbo.Invoice.Id = sub.MaxInvoice AND operation_outer.Id = sub.OperationID)
ORDER BY operation_outer.Id

The thing is in differentiating to what exactly dbo.Operation table is which column connected.

For example when you wrote WHERE dbo.Operation.Id = dbo.Operation.CorectiveId it was as columns from the same table and query has been connected together (while you, as I understood it, wanted to connect inner query to outer query at that point).

Therefor, I gave tables aliases operation_inner and operation_outer, to differentiate to which table what column belongs.

i hope it's working as you want it.