Reference a outer column in a Dervied Table function

Question

Hi, I have a query with a derived table and I need to reference a column from the outer query in a function within the derived table which uses that column as an input.

SELECT  a.Col1,
		b.Col3,
		c.Col5, 
		derived.dCol1,
		derived.dCol2,
		derived.dCol3
FROM dbo.tblA AS a
	INNER JOIN dbo.tblB AS b 
	  ON a.Col2 = b.Col2
	INNER JOIN dbo.tblC AS c 
	  ON a.Col2 = c.Col2
	INNER JOIN (SELECT dCol1,
					   dCol2,
					   dCol3
	FROM dbo.tblD AS td 
	INNER JOIN dbo.tblE AS te
	  ON td.Col1 = te.Col1 
	INNER JOIN dbo.tblM AS tm 
	  ON tm.Col1 = td.Col1 
	  AND dbo.fn_Calc(a.Col1, b.Col3, c.Col5)
	INNER JOIN dbo.tblF AS tf 
	  ON tf.Col1 = td.Col1) AS derived
	  ON derived.dCol1 = a.Col1
	  AND derived.dCol3 = a.Col5

Query looks something like above.

Thanks in advance.....................

Answer 1

Better to use Cross Apply in this case. But what is the dn_calc supposed to do?

Is it returning something?

SELECT  a.Col1,
		b.Col3,
		c.Col5, 
		derived.dCol1,
		derived.dCol2,
		derived.dCol3
FROM 
	dbo.tblA AS a
	INNER JOIN dbo.tblB AS b 
	  ON a.Col2 = b.Col2
	INNER JOIN dbo.tblC AS c 
	  ON a.Col2 = c.Col2
	Cross Apply
		(SELECT dCol1,
				dCol2,
				dCol3
		FROM dbo.tblD AS td 
			INNER JOIN dbo.tblE AS te
				ON td.Col1 = te.Col1 
			INNER JOIN dbo.tblM AS tm 
				ON tm.Col1 = td.Col1 
				AND dbo.fn_Calc(a.Col1, b.Col3, c.Col5) = ???
			INNER JOIN dbo.tblF AS tf 
				ON tf.Col1 = td.Col1
		Where 
			dCol1 = a.Col1
			And dCol3 = a.Col5
		) AS derived

Answer 2

Is the function scalar function? if so, you may want to try this

SELECT  a.Col1, b.Col3, c.Col5,  derived.dCol1, derived.dCol2, derived.dCol3, derived.fn
FROM dbo.tblA AS a
	INNER JOIN dbo.tblB AS b  ON a.Col2 = b.Col2
	INNER JOIN dbo.tblC AS c  ON a.Col2 = c.Col2
	INNER JOIN (
				SELECT dCol1, dCol2, dCol3, dbo.fn_Calc(a.Col1, b.Col3, c.Col5) fn
				FROM dbo.tblD AS td 
					INNER JOIN dbo.tblE AS te ON td.Col1 = te.Col1 
					INNER JOIN dbo.tblM AS tm ON tm.Col1 = td.Col1 
					INNER JOIN dbo.tblF AS tf  ON tf.Col1 = td.Col1
				) AS derived  ON derived.dCol1 = a.Col1 AND derived.dCol3 = a.Col5

---

Regards

John Huang, MVP-SQL, MCM-SQL, http://www.sqlnotes.info

Answer 3

Hi, Thanks all for your reponse, this is a scalar function which gives me a value to compare.

dbo.fn_Calc(a.Col1, b.Col3, c.Col5)  BETWEEN <Low Value> AND <High Value>

Regards.............

Answer 4

Hi, Thanks all for your reponse, this is a scalar function which gives me a value to compare.

dbo.fn_Calc(a.Col1, b.Col3, c.Col5)  BETWEEN <Low Value> AND <High Value>

Regards.............

The values that you want to compare are based some values from other tables or passed by user:

SELECT  a.Col1,
		b.Col3,
		c.Col5, 
		derived.dCol1,
		derived.dCol2,
		derived.dCol3
FROM 
	dbo.tblA AS a
	INNER JOIN dbo.tblB AS b 
	  ON a.Col2 = b.Col2
	INNER JOIN dbo.tblC AS c 
	  ON a.Col2 = c.Col2
	Cross Apply
		(SELECT dCol1,
				dCol2,
				dCol3
		FROM dbo.tblD AS td 
			INNER JOIN dbo.tblE AS te
				ON td.Col1 = te.Col1 
			INNER JOIN dbo.tblM AS tm 
				ON tm.Col1 = td.Col1 				
			INNER JOIN dbo.tblF AS tf 
				ON tf.Col1 = td.Col1
		Where 
			dCol1 = a.Col1
			And dCol3 = a.Col5
		) AS derived
Where dbo.fn_Calc(a.Col1, b.Col3, c.Col5) Between 1 And 10

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 5

Hi Arbi, I get those values from the table. In your very first response you had this

dbo.fn_Calc(a.Col1, b.Col3, c.Col5) = ???
change it to

dbo.fn_Calc(a.Col1, b.Col3, c.Col5)  BETWEEN d.Col6 AND d.Col7

Thanks..........

Answer 6

So, it would be something like this:

SELECT  a.Col1,
		b.Col3,
		c.Col5, 
		derived.dCol1,
		derived.dCol2,
		derived.dCol3
FROM 
	dbo.tblA AS a
	INNER JOIN dbo.tblB AS b 
	  ON a.Col2 = b.Col2
	INNER JOIN dbo.tblC AS c 
	  ON a.Col2 = c.Col2
	Cross Apply
		(SELECT dCol1,
				dCol2,
				dCol3
		FROM dbo.tblD AS td 
			INNER JOIN dbo.tblE AS te
				ON td.Col1 = te.Col1 
			INNER JOIN dbo.tblM AS tm 
				ON tm.Col1 = td.Col1 
				AND dbo.fn_Calc(a.Col1, b.Col3, c.Col5) Between td.col6 And td.col7
			INNER JOIN dbo.tblF AS tf 
				ON tf.Col1 = td.Col1
		Where 
			dCol1 = a.Col1
			And dCol3 = a.Col5
		) AS derived

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 7

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etC. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect. 

This is minimal polite behavior on a SQL forum. 

Good SQL programmers do not use dialect UDF to screw the optimizers. We also do not use infixed join syntax. This thing ought to be a simple six table select. Why did you nest INNER JOINs? 

Since you are only willing to post a vague skeleton, here is a vague skeleton answer. 

SELECT A.col1, B.col3, C.col5,  D.col1, E.col2, F.col3
  FROM A, B, C, D, E, F, M
 WHERE A.col2 = B.col2
   AND A.col2 = C.col2
   AND D.col1 = E.col1 
   AND M.col1 = D.col1 
   AND F.col1 = D.col1  etc

Notice there is no "dbo.fn_Calc(A.col1, B.col3, C,col5)" in this. Good programmers would not write that garbage.  Want to try again? With minimal netiquette, pease. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

Hi,

My suggestion is to split tbis query in two CTE's and then try to join them...

---

------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

Answer 9

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etC. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect. 

This is minimal polite behavior on a SQL forum. 

Good SQL programmers do not use dialect UDF to screw the optimizers. We also do not use infixed join syntax. This thing ought to be a simple six table select. Why did you nest INNER JOINs? 

Since you are only willing to post a vague skeleton, here is a vague skeleton answer. 

SELECT A.col1, B.col3, C.col5,  D.col1, E.col2, F.col3
  FROM A, B, C, D, E, F, M
 WHERE A.col2 = B.col2
   AND A.col2 = C.col2
   AND D.col1 = E.col1 
   AND M.col1 = D.col1 
   AND F.col1 = D.col1  etc

Notice there is no "dbo.fn_Calc(A.col1, B.col3, C,col5)" in this. Good programmers would not write that garbage.  Want to try again? With minimal netiquette, pease. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

I'm staggered that there is still so much of this chest beating nonsense going on....