Select T1.Field1 ,
T1.Field2
from Table1 T1
Left join Table2 T2 on T1.Filed1=T2.Field1
LEFT JOIN
(
SELECT FiELD,AVG([Some Numeric Field])
from TABLE2
GROUP BY FiELD
) C ON C.FiELD=T1.Field1
SELECT
A.SOLDDATE,
A.MORTAGEDATE,
A.Currency,
COALESCE(B.Forex,
C.Forex,
(SELECT AVG(FOREX) FROM TABLE2 S WHERE (S.TO = 'RS') and
(A.Currency = S.FROM))) AS PriceRS
FROM TABLE1 A
LEFT JOIN TABLE2 B on A.SOLDDATE = B.FOREXDATE
LEFT JOIN TABLE2 C on A.MortgageDate = C.ForexDate
saadat Ali
Member
60 Points
57 Posts
The multi-part identifier "alias.field" could not be bound
May 03, 2012 12:36 PM|LINK
I am doing a left join and making a cross join on the result
It's something like following
Select T1.Field1 , T1.Field2 from Table1 T1
Left join Table2 T2
on T1.Filed1=T2.Field1
CROSS JOIN (SELECT AVG([Some Numeric Field]) from TABLE2 WHERE FiELD=T1.Field1) C
ERROR: I am getting an ERROR in my CROSS JOIN QUERY, Its not able to recognise T1.Field1
Can anybdy suggest how i can resolve it
yrb.yogi
Star
14460 Points
2402 Posts
Re: The multi-part identifier "alias.field" could not be bound
May 03, 2012 01:04 PM|LINK
.Net All About
limno
All-Star
117336 Points
8003 Posts
Moderator
MVP
Re: The multi-part identifier "alias.field" could not be bound
May 03, 2012 01:07 PM|LINK
Can you post some sample data and your expected result from your query? Thanks.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
saadat Ali
Member
60 Points
57 Posts
Re: The multi-part identifier "alias.field" could not be bound
May 03, 2012 01:26 PM|LINK
TABLE 1 TABLE 2
SOLD_DATE MORTAGE_DATE TOTALPRICE Currency FOREXDATE FOREXRATE FROM TO
01-01-2001 01-02-2001 100000 AUS 01-01-2001 0.000255 AUS RS
Now the Scenario is ALL I need to convert TOTALPRICE to RS either
1) IF there is a match on SOLDDATE =FOREXDATE OR IF this does not EXIST THEN
2. MORTAGEDATE= FOREX DATE
IF BOTH fails then i want to CAlCULATE AVERAGE
What i have done is
SELECT A. SOLDDATE, A. MORTAGE DATE, A.Currency, ISNULL(B.Forex,C.Forex)
LEFT JOIN TABLE2 B on A.SOLDDATE = B.FOREXDATE
LEFT JOIN TABLE2 C on A.MortgageDate=C.ForexDate
-- -- This works up to this point But i want to calculate the Average if Both Left joins still brinf NULL as ForexValue ---
--- Following is what i am doing---
---- Making a CROSS join with above ---
CROSS JOIN (SELECT AVG(FOREX) FROM TABLE2 WHERE FROM = A. Currency and TO='RS')
I am getting on Error where i reference A. Currency in my CROSS join
gimimex
Participant
1052 Points
157 Posts
Re: The multi-part identifier "alias.field" could not be bound
May 03, 2012 01:42 PM|LINK
Try something like this:
SELECT A.SOLDDATE, A.MORTAGEDATE, A.Currency, COALESCE(B.Forex, C.Forex, (SELECT AVG(FOREX) FROM TABLE2 S WHERE (S.TO = 'RS') and (A.Currency = S.FROM))) AS PriceRS FROM TABLE1 A LEFT JOIN TABLE2 B on A.SOLDDATE = B.FOREXDATE LEFT JOIN TABLE2 C on A.MortgageDate = C.ForexDateHope this helps.