Pass parameter to subquery

Question

I'm using SSRS/SSDT in Visual Studio 2015 and SQL Server 2014. There's a bug that's been present for > 8 years where you can't select multiple columns from different tables that have the same name. To get around this, I need to use a subquery. Every single answer I find rewrites the given query to remove the subquery, which would normally be great but is not applicable in this case. How do I pass a parameter to a subquery in SQL Server?

Column aliases do not work with this bug--Using AS returns an unknown column error on the "duplicate" columns even though it works with all others. The last two lines in the SELECT clause work because the values are being queried so the report can use them, but the remainder of the actual query doesn't use them.

Here's my current code (doesn't work because the subquery returns multiple rows).

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND
(
    SELECT StatusId FROM Project
    -- WHERE ?
)
NOT IN (3, 4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

This is the code with aliases as requested in the comments. It throws an error:

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId AS TaskStatusId,
       t.PriorityId,
       p.ProjectNumber,
       p.StatusId AS ProjectStatusId,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = TaskStatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND TaskStatusId NOT IN (4,7)
AND ProjectStatusId NOT IN (3,4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'ProjectStatusId'.
-- Invalid column name 'ProjectStatusId'.

The ideal code is below, but it throws the error An item with the same key has already been added, which is the error that SSRS/SSDT throws when trying to return multiple columns of the same name.

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       p.StatusId,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND p.StatusId NOT IN (3,4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

just give `p.StatusId` a new name like `p.StatusId as ProjectStatusId` or something — JamieD77, Feb 17 '16 at 15:13
@JamieD77 That doesn't work with SSRS, I just edited the question to clarify this. — vaindil, Feb 17 '16 at 15:14
Why is your question about "passing a parameter" when there are no parameters in your example queries? — Tab Alleman, Feb 17 '16 at 15:19
@TabAlleman I need to filter on both `t.StatusId` and `p.StatusId`. I need to work around the bug in SSRS. To do so, I need to use the subquery. I don't mean I need to pass a parameter from the original query, I mean I need to pass an (as yet unspecified) parameter to the subquery. — vaindil, Feb 17 '16 at 15:21
Your "ideal code" WILL WORK, but you need to use "AS" on the `StatusID` columns as well as the `Name` columns. Having 2 `StatusID` columns is why you're still getting the error. — Tab Alleman, Feb 17 '16 at 15:35
if you replace `TaskStatusId` with `t.TaskStatusId` and `ProjectStatusId` with `p.StatusId` everywhere below `FROM` your second query should work.. — JamieD77, Feb 17 '16 at 22:44

score 6 · Accepted Answer · answered Feb 17 '16 at 15:35

6

I feel very stupid for this, but apparently it works to just call p.Id in the subquery. It knows that I'm referencing the parent query's value even though it's in the subquery. Thank you everyone for your help.

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND
(
    SELECT StatusId FROM Project WHERE Id = p.Id
)
NOT IN (3, 4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

answered Feb 17 '16 at 15:35

vaindil

7,536
21
68
127

This has nothing to do with your original problem. This works because you've removed p.StatusId from the Select list. – Tab Alleman Feb 17 '16 at 15:40
That's why this fixes the problem. I guess you're correct, it doesn't involve passing a parameter like the question originally wanted, but it solves the problem. I can rework the question to better fit. – vaindil Feb 17 '16 at 15:43
Your problem is that you're trying to use the field aliases in your JOINS and WHERE statement.. Just because you say `t.StatusId AS TaskStatusID` in your SELECT does not mean you can use `TaskStatusID` in `inner join Project_TaskStatus s on s.Id = TaskStatusId` you have to use the field name like `inner join Project_TaskStatus s on s.Id = t.StatusID`. The only place you can use the `ALIAS` is in the `ORDER BY` – JamieD77 Feb 17 '16 at 22:39
No need to feel stupid :) this entire site exists because people don't know everything. – Ben Jul 07 '23 at 20:32

radoh · Answer 2 · 2016-02-17T15:34:54.457

1

current code (doesn't work because the subquery returns multiple rows).

So instead of this

AND
(
    SELECT StatusId FROM Project
    -- WHERE ?
)
NOT IN (3, 4)

You could do

AND
EXISTS (
    SELECT 1 FROM Project p2
    WHERE p2.StatusId IN (3, 4) AND p2.Id = p.Id
)

edited Feb 17 '16 at 15:34

answered Feb 17 '16 at 15:23

radoh

4,554
5
30
45

That will select the top row in the `Project` table that has a `StatusId` of 3 or 4, it won't check based on the project that the task is associated with. – vaindil Feb 17 '16 at 15:25
@Vaindil Ok, I've updated my answer. Won't that work? (I work with oracle, I'm not really familiar with sql server...) – radoh Feb 17 '16 at 15:35

score 1 · Answer 3 · answered Feb 17 '16 at 15:37

Try this:

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId AS TaskStatusId,
       t.PriorityId,
       p.ProjectNumber,
       p.StatusId AS ProjectStatusId,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND p.StatusId NOT IN (3,4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

score 0 · Answer 4 · answered Feb 17 '16 at 15:17

0

The fix is easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together

answered Feb 17 '16 at 15:17

GabrielVa

2,353
9
37
59

I need to use both fields. Using `AS` doesn't work, I've added another code block to show this. – vaindil Feb 17 '16 at 15:19
SSRS only looks at column names while determining fields for your dataset, and throws this error if you SELECT two columns with the same name from one or more tables. – GabrielVa Feb 17 '16 at 15:23
I understand, that's what I'm trying to work around. I would like to avoid having to rename the columns themselves. – vaindil Feb 17 '16 at 15:26
You can also try using a CTE to create a subquery if you are still having issues. – GabrielVa Feb 17 '16 at 15:26

Pass parameter to subquery

4 Answers4