Forum Discussion

AmaniMRA's avatar
AmaniMRA
Copper Contributor
Apr 13, 2022

SQL condition based on a select subquery column not bounding

Hello,

I have a request for help regarding an SQL query that drive me crazy.

Using MSSQL, I want to apply a condition based on a select subquery. How can I do it optimally ?

Here is my initial query :

 

SELECT tblA.Col, tblB.PlacementDate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 PlacementDate 
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA

 

And I want to add WHERE condition using table t ( UNION result ) in order to select today's data : I want to select `TOP 1 PlacementDate` and use `PlacementDate` as to retrieve today's data. The final query look like :

 

SELECT tblA.Col, tblB.PlacementDate, tblC.CCol
FROM tblA
OUTER APPLY (SELECT TOP 1 PlacementDate 
             FROM ( SELECT PlacementDate from TAB1 WHERE TAB1.Col3 = tblA.ACol UNION SELECT PlacementDate FROM TAB2 WHERE TAB2.Col3 = tblA.ACol) t 
             ORDER BY PlacementDate) tblB
INNER JOIN tblC on tblC.ColNameC = tblA.ColNameA
WHERE CAST(t.PlacementDate AS DATE) = CAST(getdate() AS DATE)

 

But I can't reference the column PlacementDate from t and I have this error message appearing :

 

The multi part identifier 't.PlacementDate' could not be bound

 

2 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Apr 14, 2022

    AmaniMRA ,

     

    you get the error message because "t." is the query alias for the inner sub-query, the alias for the outer query is tblB =>

     

     

     

    WHERE CAST(tlbB.PlacementDate AS DATE) = CAST(getdate() AS DATE)

     

     

     

    • AmaniMRA's avatar
      AmaniMRA
      Copper Contributor
      Apr 14, 2022

      olafhelperWhen using "tlbB.PlacementDate" it will refer to "TOP 1 PlacementDate" from "tlbB" and so the logic will be "WHERE TOP 1 PlacementDate = today's date" which is not the logic that I'm searching for ( query tested and returning unexpected result ).

      The correct logic is : "WHERE PlacementDate ( from the union table ) = today's date". This is what I'm enable to do. Please advise

       

Resources