The multi-part identifier “t7.rate_buy” could not be bound

Please help me on this error message:

The multi-part identifier “t7.rate_buy” could not be bound.

I can’t find how to fix this. I use SQL Server 2012 and I have tried everything but I can’t figure out what the solution is.

Thank you

 SELECT t1.id, t1.q_no
     , (
         SELECT rate_buy
         FROM [dta].[dbo].[rates_exchange]
         WHERE date_r=t1.q_date
     ) AS t7
     , t1.account_name
     , (
         SELECT SUM(t1.maden/ t7.rate_buy) AS ex32
         FROM [dta].[dbo].[qyed] AS t5
         WHERE t1.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS m_f
     , (
         SELECT SUM(t1.daen/(t7.rate_buy)) AS ex33
         FROM [dta].[dbo].[qyed] AS t6
         WHERE t1.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS d_f
     , t1.third_id, t1.maden,t1.daen, t1.bayen, t1.q_date, t1.cur
     , (
         SELECT SUM(t1.maden/t7.rate_buy - t1.daen/t7.rate_buy) AS Expr5
         FROM [dta].[dbo].[qyed] AS t2
         WHERE (id <= t1.id) AND t1.[q_date] BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS tot
     , t1.snd_type, t1.r_no
 FROM [dta].[dbo].[qyed] t1
 WHERE t1.third_id = 'F1' AND t1.[q_date] BETWEEN '01/01/2000' AND '01/01/2021'
 GROUP BY t1.id, t1.q_no, t1.account_name, t1.q_date, t1.third_id, t1.snd_type, t1.r_no, t1.daen, t1.maden, t1.bayen, t1.cur
        

Answer

You cannot reference another calculated column at the same level of the query as its defined (except for ordering). One solution is to calculate rate_buy as a sub-query e.g.

 SELECT t1.id, t1.q_no
     , t1.account_name
     , (
         SELECT SUM(t1.maden/t1.rate_buy) AS ex32
         FROM [dta].[dbo].[qyed] AS t5
         WHERE t1.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS m_f
     , (
         SELECT SUM(t1.daen/(t1.rate_buy)) AS ex33
         FROM [dta].[dbo].[qyed] AS t6
         WHERE t1.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS d_f
     , t1.third_id, t1.maden, t1.daen, t1.bayen, t1.q_date, t1.cur
     , (
         SELECT SUM(t1.maden/t1.rate_buy - t1.daen/t1.rate_buy) AS Expr5
         FROM [dta].[dbo].[qyed] AS t2
         WHERE (id <= t1.id) AND t1.[q_date] BETWEEN '01/01/2000' AND '01/01/2021' AND t1.third_id = 'F1'
     ) AS tot
     , t1.snd_type, t1.r_no
 FROM (
     SELECT *
         , (
             SELECT rate_buy
             FROM [dta].[dbo].[rates_exchange]
             WHERE date_r = t1.q_date
         ) AS rate_buy
     FROM [dta].[dbo].[qyed]
 ) AS t1
 WHERE t1.third_id = 'F1' AND t1.[q_date] BETWEEN '01/01/2000' AND '01/01/2021'
 GROUP BY t1.id, t1.q_no, t1.account_name, t1.q_date, t1.third_id, t1.snd_type, t1.r_no, t1.daen, t1.maden, t1.bayen, t1.cur;

Given you reference t1 in your 3 sub-queries, that removes the point of having them. I suspect you actually want the following, where t5, t6 & t2 are actually used.

 SELECT t1.id, t1.q_no
     , t1.account_name
     , (
         SELECT SUM(t5.maden/t1.rate_buy)
         FROM [dta].[dbo].[qyed] AS t5
         WHERE t5.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t5.third_id = 'F1'
     ) AS m_f
     , (
         SELECT SUM(t6.daen/(t1.rate_buy))
         FROM [dta].[dbo].[qyed] AS t6
         WHERE t6.q_date BETWEEN '01/01/2000' AND '01/01/2021' AND t6.third_id = 'F1'
     ) AS d_f
     , t1.third_id, t1.maden, t1.daen, t1.bayen, t1.q_date, t1.cur
     , (
         SELECT SUM(t2.maden/t1.rate_buy - t2.daen/t1.rate_buy)
         FROM [dta].[dbo].[qyed] AS t2
         WHERE (t2.id <= t1.id)
         AND t2.[q_date] BETWEEN '01/01/2000' AND '01/01/2021' AND t2.third_id = 'F1'
     ) AS tot
     , t1.snd_type, t1.r_no
 FROM (
     SELECT *
         , (
             SELECT rate_buy
             FROM [dta].[dbo].[rates_exchange]
             WHERE date_r = t1.q_date
         ) AS rate_buy
     FROM [dta].[dbo].[qyed]
 ) AS t1
 WHERE t1.third_id = 'F1' AND t1.[q_date] BETWEEN '01/01/2000' AND '01/01/2021'
 GROUP BY t1.id, t1.q_no, t1.account_name, t1.q_date, t1.third_id, t1.snd_type, t1.r_no, t1.daen, t1.maden, t1.bayen, t1.cur;

However if that is what you want then you can probably use window function sum rather than a sub-query. I would have a go, but without sample data I can’t work out how that would interact with your massive group by.