SELECT TR_1.*, RE_1.*
FROM
(SELECT TR_1.ACCOUNT, TR_1.ACTIVITY,TR_1.DONOR,TR_1.COSTCENTER,
SUM(CASE WHEN TR_1.TTYPE = 'U' THEN TR_1.AmountLCU*-1 ELSE 0 END) AS Budget,
SUM(CASE WHEN TR_1.TTYPE <> 'U' THEN TR_1.AmountLCU*-1 ELSE 0 END) AS Actual,
TR_1.TransactionDate [TR_1 Date], 
YEAR(TR_1.TransactionDate) [TR_1 Year]
FROM scalaDB.dbo.A_GL0601_PREVIOUS AS TR_1
WHERE NOT (TR_1.TTYPE='\' OR TR_1.TTYPE='a' OR TR_1.TTYPE='c') 
AND NOT (TR_1.COSTCENTER=N'' OR TR_1.COSTCENTER=N'0000') AND ((TR_1.ACCOUNT>=N'26' AND TR_1.ACCOUNT<N'7100') OR (TR_1.ACCOUNT>N'7100' AND TR_1.ACCOUNT<=N'7999'))
GROUP BY TR_1.COSTCENTER,TR_1.ACCOUNT,TR_1.ACTIVITY,TR_1.DONOR,TR_1.TransactionDate)tmp LEFT OUTER JOIN 
(SELECT RE_1.ACT [RE-ACT],RE_1.DONOR [RE-DONOR],RE_1.CC RE-CC,YEAR(RE_1.DTE) [RE-1 Year],RE_1.SYMBOL,RE_1.DTE,RE_1.SPLIT_AMOUNT AS [RE Split in CHF],RE_1.FOREIGN_AMOUNT AS [RE Foreign Amnt] FROM scalaDB.dbo.RE_Donor_Gift) RE_1
ON
tmp.COSTCENTER=RE_1.CC AND tmp.DONOR=RE_1.DONOR AND tmp.ACTIVITY=RE_1.ACT AND [TR_1 Year]=[RE-1 Year]

quote:

---

Originally posted by ishaame

Sorry Peso, but I get the similar error message: RE does not match with table name or alias name used in the query

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by ishaame

Sorry Peso, but I get the similar error message: RE does not match with table name or alias name used in the query

---

Can you post your full query used?

---

quote:

---

Originally posted by ishaame

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by ishaame

Sorry Peso, but I get the similar error message: RE does not match with table name or alias name used in the query

---

Can you post your full query used?

---

What I posted is the full query

---

SELECT		TR.*,
		RE.*
FROM		(
			SELECT		ACCOUNT,
					ACTIVITY,
					DONOR,
					COSTCENTER,
					SUM(CASE WHEN TTYPE = 'U' THEN -AmountLCU ELSE 0 END) AS Budget,
					SUM(CASE WHEN TTYPE <> 'U' THEN -AmountLCU ELSE 0 END) AS Actual,
					TRANSACTIONDATE AS [TR_1 Date],
					YEAR(TRANSACTIONDATE) AS [TR_1 Year]
			FROM		ScalaDB.dbo.A_GL0601_PREVIOUS
			WHERE		TTYPE NOT IN ('\', 'a', 'c')
					AND COSTCENTER NOT IN ('', '0000')
					AND ACCOUNT >= '26'
					AND ACCOUNT <= '7999'
					AND ACCOUNT <> '7100'
			GROUP BY	COSTCENTER,
					ACCOUNT,
					ACTIVITY,
					DONOR,
					TRANSACTIONDATE
		) AS TR
LEFT JOIN	(
			SELECT		ACT AS [RE-ACT],
					DONOR AS [RE-DONOR],
					CC AS [RE-CC],
					YEAR(RE_1.DTE) AS [RE-1 Year],
					SYMBOL,
					DTE,
					SPLIT_AMOUNT AS [RE Split in CHF],
					FOREIGN_AMOUNT AS [RE Foreign Amnt]
			FROM		scalaDB.dbo.RE_Donor_Gift
		) AS RE ON RE.[RE-CC]= TR.COSTCENTER
			AND RE.DONOR = TR.DONOR
			AND RE.[RE-ACT] = TR.ACTIVITY
			AND RE.[RE-1 Year] = TR.[TR_1 Year]

quote:

---

Originally posted by ishaame

Hello everybody,
My query works now. The problem was that it did not like to refer table alias before the alias was defined. For example
SELECT TR.*
FROM (SELECT
TR.DONOR
FROM GL06_PREVIOUS
) AS TR
would not work,
but

SELECT TR.*
FROM (SELECT
DONOR
FROM GL06_PREVIOUS
) AS TR

Thanks everybody for the help.
IShaame

---