Hi,
I have a query that runs fine in SQL Server Studio, returning the
single username as it is meant to. However, when I place this query in
a cfquery tag I get zero results returned. Both are hitting the same
datasource using the same account. I'm wondering whether the "with"
clause or the table variable is unsupported. Or maybe it is the
recursive nature of the "with" clause that isn't working in openbd?
DECLARE @group_list TABLE
(
group_id int
);
with group_hierarchy ( parent_group, child_group, g_name )
as
( select g1.group_id, g2.member_group_id, g1.group_name from
rtsb.rt_groups_groups g2
join rtsb.rt_groups g1 on g1.group_id = g2.group_id
where g1.group_id = 7316
union all
select g1.group_id, g2.member_group_id, g1.group_name from
rtsb.rt_groups_groups g2
join rtsb.rt_groups g1 on g1.group_id = g2.group_id
join group_hierarchy gh on g1.group_id = gh.child_group
)
INSERT INTO @group_list (group_id)
(select distinct id from
(select distinct parent_group as 'id' from group_hierarchy
union all
select distinct child_group as 'id' from group_hierarchy)
s);
select distinct u.username from rtsb.rt_users u
LEFT OUTER JOIN rtsb.rt_users_groups ug ON u.site_id = ug.site_id
AND u.user_id = ug.user_id
LEFT OUTER JOIN rtsb.rt_metadata md ON u.user_id = md.item_id AND
md.table_id = 6
where
( u.primary_group_id in (select * from @group_list)
or ug.group_id in (select * from @group_list))
and u.username = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#trim(Session.username)#" />
AND (md.status = 1 OR md.status is NULL);
--
official tag/function reference: http://openbd.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
