May 27, 2009 at 12:57 pm
Given that I cannot change the DDL, must follow company standards (one of which is NO temp tables) and must work with what I'm given, here's the issue...
Large multi-UNION query.
Two parts of the query use results from the same table-valued function.
So, I've set it up so that the TF is a CTE thusly:
WITH UserGroups (group_id) AS
(select group_id from f_get_groups_user(@user_id))
Works fine for the most part, but then I get to this mess:
SELECT G.action_id
FROM
UserGroups gp,
user_role_action G
INNER JOIN
user_role H
ON
G.role_id = H.role_id
CROSS APPLY
f_get_roles_group(gp.group_id) grg
WHERE
G.role_id = grg.role_id
AND (@dept_id = -1 or department_id = @dept_id)
Which obviously doesn't work.
I know I'm just being blind on this one and that the solution is staring me in the face, but I'm not seeing it. HALP?
DDL is a mess so I'm hoping you can get what's going on from what I've got here.
May 27, 2009 at 1:23 pm
I think part of your problem is the mixing of join syntax. You are using both new and old style joins. First thing I'd do is rewrite the query using only ANSI style (new) join syntax.
May 27, 2009 at 1:33 pm
Yeah, I know I am and I've tried to figure that out. Problem is, UserGroups doesn't *join* to anything and CROSS APPLY doesn't use JOIN.
May 27, 2009 at 1:44 pm
Absolutely NO GUARANTEES on this code. I have nothing to test it against.
WITH UserGroups (
group_id
) AS (
select
group_id
from
f_get_groups_user(@user_id)
)
SELECT
G.action_id
FROM
user_role_action G
INNER JOIN user_role H
ON (G.role_id = H.role_id )
CROSS JOIN (UserGroups gp
CROSS APPLY f_get_roles_group(gp.group_id) grg)
WHERE
G.role_id = grg.role_id
AND (@dept_id = -1
or department_id = @dept_id)
May 27, 2009 at 5:30 pm
I have some more testing to do but it looks like that will do the trick. Thanks.
May 28, 2009 at 11:14 am
Just a follow-up, how is your testing going?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply