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