)URA
	LEFT OUTER JOIN
	(
		SELECT CASE WHEN From = 1 THEN LID ELSE HID END AS FID, CASE WHEN From = 0 THEN LID ELSE HID END AS URAID
		FROM table1 LRE
	)FS
	ON URA.URAID = FS.URAID
	LEFT OUTER JOIN 
	(
		SELECT CASE WHEN From = 1 THEN LID ELSE HID END AS FSID, CASE WHEN From = 1 THEN HID ELSE LID END AS DSID, ID
		FROM table1 LRE
		INNER JOIN table2 WI
		ON (LRE.DSID = WI.ID)
			WHERE Fld100 = 'Operational'
				AND [WorkType] = @WorkItemType	
				AND State <> 'Closed'
	)DS
	ON DS.FSID = DS.FSID

quote:

---

Originally posted by sqlpal2007

Hello All,

The below inner query give me the error Invalid column name 'DSID'.

Can any one tell me what is wrong?

	
)URA
	LEFT OUTER JOIN
	(
		SELECT CASE WHEN From = 1 THEN LID ELSE HID END AS FID, CASE WHEN From = 0 THEN LID ELSE HID END AS URAID
		FROM table1 LRE
	)FS
	ON URA.URAID = FS.URAID
	LEFT OUTER JOIN 
	(
		SELECT CASE WHEN From = 1 THEN LID ELSE HID END AS FSID, CASE WHEN From = 1 THEN HID ELSE LID END AS DSID, ID
		FROM table1 LRE
		INNER JOIN table2 WI
		ON (LRE.DSID = WI.ID)
			WHERE Fld100 = 'Operational'
				AND [WorkType] = @WorkItemType	
				AND State <> 'Closed'
	)DS
	ON DS.FSID = DS.FSID

Thanks,
-P

---

 ON DS.FSID = FS.FSID 

 ON (LRE.DSID = WI.ID)