Can't figure out the right syntax

  • 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.

  • 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.

  • 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.

  • 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)

  • I have some more testing to do but it looks like that will do the trick. Thanks.

  • 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