CODEWITHSUNDEEP
sqlsql-serverreporting-services
vaindil
vaindil

Reputation: 7864

Pass parameter to subquery

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

Upvotes: 3

Views: 44621

Answers (4)

Tab Alleman
Tab Alleman

Reputation: 31785

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

Upvotes: 1

vaindil
vaindil

Reputation: 7864

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

Upvotes: 6

radoh
radoh

Reputation: 4815

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
)

Upvotes: 1

GabrielVa
GabrielVa

Reputation: 2388

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

Upvotes: 0

Related Questions