SQLTeam.com | Weblogs | Forums

Help with outer apply


#1

Hello i am struggling getting this query run. I'm getting this error "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression." Thanks in advance!

DECLARE @ID int = 2;

SELECT
e.ID,
u1a_name,
u1b_name,
u1c_name,
u1d_name,
u1e_name,

u2a_name,
u2b_name,
u2c_name,
u2d_name,
u2e_name,

u3a_name,
u3b_name,
u3c_name,
u3d_name,
u3e_name,

u4a_name,
u4b_name,
u4c_name,
u4d_name,
u4e_name,

u5a_name,
u5b_name,
u5c_name,
u5d_name,
u5e_name,

u6a_name,
u6b_name,
u6c_name,
u6d_name,
u6e_name,

u1a_color,
u1b_color,
u1c_color,
u1d_color,
u1e_color,

u2a_color,
u2b_color,
u2c_color,
u2d_color,
u2e_color,

u3a_color,
u3b_color,
u3c_color,
u3d_color,
u3e_color,

u4a_color,
u4b_color,
u4c_color,
u4d_color,
u4e_color,

u5a_color,
u5b_color,
u5c_color,
u5d_color,
u5e_color,

u6a_color,
u6b_color,
u6c_color,
u6d_color,
u6e_color,
oa1.*

FROM eMat e

OUTER APPLY
(

SELECT
MAX(CASE WHEN e.u1a = derived.id THEN derived.[Standard] END) AS u1a_name,
MAX(CASE WHEN e.u1b= derived.id THEN derived.[Standard] END) AS u1b_name,
MAX(CASE WHEN e.u1c= derived.id THEN derived.[Standard] END) AS u1c_name,
MAX(CASE WHEN e.u1d= derived.id THEN derived.[Standard] END) AS u1d_name,
MAX(CASE WHEN e.u1e= derived.id THEN derived.[Standard] END) AS u1e_name,

MAX(CASE WHEN e.u2a= derived.id THEN derived.[Standard] END) AS u2a_name,
MAX(CASE WHEN e.u2b= derived.id THEN derived.[Standard] END) AS u2b_name,
MAX(CASE WHEN e.u2c= derived.id THEN derived.[Standard] END) AS u2c_name,
MAX(CASE WHEN e.u2d= derived.id THEN derived.[Standard] END) AS u2d_name,
MAX(CASE WHEN e.u2e= derived.id THEN derived.[Standard] END) AS u2e_name,

MAX(CASE WHEN e.u3a= derived.id THEN derived.[Standard] END) AS u3a_name,
MAX(CASE WHEN e.u3b= derived.id THEN derived.[Standard] END) AS u3b_name,
MAX(CASE WHEN e.u3c= derived.id THEN derived.[Standard] END) AS u3c_name,
MAX(CASE WHEN e.u3d= derived.id THEN derived.[Standard] END) AS u3d_name,
MAX(CASE WHEN e.u3e= derived.id THEN derived.[Standard] END) AS u3e_name,

MAX(CASE WHEN e.u4a= derived.id THEN derived.[Standard] END) AS u4a_name,
MAX(CASE WHEN e.u4b= derived.id THEN derived.[Standard] END) AS u4b_name,
MAX(CASE WHEN e.u4c= derived.id THEN derived.[Standard] END) AS u4c_name,
MAX(CASE WHEN e.u4d= derived.id THEN derived.[Standard] END) AS u4d_name,
MAX(CASE WHEN e.u4e= derived.id THEN derived.[Standard] END) AS u4e_name,

MAX(CASE WHEN e.u5a= derived.id THEN derived.[Standard] END) AS u5a_name,
MAX(CASE WHEN e.u5b= derived.id THEN derived.[Standard] END) AS u5b_name,
MAX(CASE WHEN e.u5c= derived.id THEN derived.[Standard] END) AS u5c_name,
MAX(CASE WHEN e.u5d= derived.id THEN derived.[Standard] END) AS u5d_name,
MAX(CASE WHEN e.u5e= derived.id THEN derived.[Standard] END) AS u5e_name,

MAX(CASE WHEN e.u6a= derived.id THEN derived.[Standard] END) AS u6a_name,
MAX(CASE WHEN e.u6b= derived.id THEN derived.[Standard] END) AS u6b_name,
MAX(CASE WHEN e.u6c= derived.id THEN derived.[Standard] END) AS u6c_name,
MAX(CASE WHEN e.u6d= derived.id THEN derived.[Standard] END) AS u6d_name,
MAX(CASE WHEN e.u6e = derived.id THEN derived.[Standard] END) AS u6e_name,

MAX(CASE WHEN e.u1a = derived.id THEN derived.color END) AS u1a_color,
MAX(CASE WHEN e.u1b= derived.id THEN derived.color END) AS u1b_color,
MAX(CASE WHEN e.u1c= derived.id THEN derived.color END) AS u1c_color,
MAX(CASE WHEN e.u1d= derived.id THEN derived.color END) AS u1d_color,
MAX(CASE WHEN e.u1e= derived.id THEN derived.color END) AS u1e_color,

MAX(CASE WHEN e.u2a= derived.id THEN derived.color END) AS u2a_color,
MAX(CASE WHEN e.u2b= derived.id THEN derived.color END) AS u2b_color,
MAX(CASE WHEN e.u2c= derived.id THEN derived.color END) AS u2c_color,
MAX(CASE WHEN e.u2d= derived.id THEN derived.color END) AS u2d_color,
MAX(CASE WHEN e.u2e= derived.id THEN derived.color END) AS u2e_color,

MAX(CASE WHEN e.u3a= derived.id THEN derived.color END) AS u3a_color,
MAX(CASE WHEN e.u3b= derived.id THEN derived.color END) AS u3b_color,
MAX(CASE WHEN e.u3c= derived.id THEN derived.color END) AS u3c_color,
MAX(CASE WHEN e.u3d= derived.id THEN derived.color END) AS u3d_color,
MAX(CASE WHEN e.u3e= derived.id THEN derived.color END) AS u3e_color,

MAX(CASE WHEN e.u4a= derived.id THEN derived.color END) AS u4a_color,
MAX(CASE WHEN e.u4b= derived.id THEN derived.color END) AS u4b_color,
MAX(CASE WHEN e.u4c= derived.id THEN derived.color END) AS u4c_color,
MAX(CASE WHEN e.u4d= derived.id THEN derived.color END) AS u4d_color,
MAX(CASE WHEN e.u4e= derived.id THEN derived.color END) AS u4e_color,

MAX(CASE WHEN e.u5a= derived.id THEN derived.color END) AS u5a_color,
MAX(CASE WHEN e.u5b= derived.id THEN derived.color END) AS u5b_color,
MAX(CASE WHEN e.u5c= derived.id THEN derived.color END) AS u5c_color,
MAX(CASE WHEN e.u5d= derived.id THEN derived.color END) AS u5d_color,
MAX(CASE WHEN e.u5e= derived.id THEN derived.color END) AS u5e_color,

MAX(CASE WHEN e.u6a= derived.id THEN derived.color END) AS u6a_color,
MAX(CASE WHEN e.u6b= derived.id THEN derived.color END) AS u6b_color,
MAX(CASE WHEN e.u6c= derived.id THEN derived.color END) AS u6c_color,
MAX(CASE WHEN e.u6d= derived.id THEN derived.color END) AS u6d_color,
MAX(CASE WHEN e.u6e = derived.id THEN derived.color END) AS u6e_color

FROM
(

SELECT s.ID
,s.[Standard]
,s.color
FROM standards s
WHERE s.ID IN (
e.u1a, e.u1b, e.u1c, e.u1d, e.u1e, e.u2a, e.u2b, e.u2c, e.u2d,
e.u2e, e.u3a, e.u3b, e.u3c, e.u3d, e.u3e, e.u4a, e.u4b, e.u4c, e.u4d, e.u4e,
e.u5a, e.u5b, e.u5c, e.u5d, e.u5e, e.u6a, e.u6b, e.u6c, e.u6d, e.u6e
)
) as derived

) as oa1
WHERE e.ID = @ID


#2

So it looks like a join but you have multiple columns on the left side. (e.xxx = derived.id). I see a problem though. What if there is more than one row in the standards table that matches e.xxx for some xxx?

You could do it as a bunch of joins, one for each e.xxx, that returns the name and color for that id. You could also unpivot those columns (in eMat) and do one join to get the matches, then pivot the result to get the separate columns back. A bunch of CROSS APPLYs might do it also:

...
CROSS APPLY (SELECT derived.name, derived.name where e.xxx = derived.id) as xxx_val
...

for some xxx, then in the top select:

select xxx_val.color, xxx_val.name