I have come across similar problems with executing complicated SQL. One
solution could be to place this in a view, and as the sg.support_group_nameand
sg.company are in the select list these can be moved to the where clause of
the select of the view.
eg. select * from ctm_support_group_view where company = 'UNINA' and
support_group_name like 'UNI-NA%'
This isn't a python solution, and of course I don't know how dynamic you
want these selects to be and if you will need to create thse views on the
fly either, and maintaining these views creates more work for your DBAs ...
James
On 11/28/06, Christopher Eckman <[EMAIL PROTECTED]> wrote:
Hi all,
I am doing a select to concatenate a number of entries into a field like
this under 'operators' (sample header is the first line):
name company uis_access_control uis_tp_ticketpassing operators
UNINA FOO unrestricted
No
uni-catherine_srvage,uni-robert_woyzik,uni-susan_fooman
using the SQL Server functionality cross apply and for xml. Sample select
is below:
select support_group_name "name", sg.Company "company",
sg.f5"uis_access_control",
sg.f6 "uis_tp_ticketpassing", sg.REZ_Manager "manager",
substring(memberList, 1, datalength(memberList)/2 - 1)
"operators"
-- strip the last ',' from the list
from
ctm_support_group sg cross apply
(select convert(nvarchar(60), sgm.support_group_member_name) + ','
as [text()]
from tsmi_support_group_members sgm
where sg.Support_Group_ID = sgm.Support_Group_ID and sg.Company =
'UNINA' and sg.support_group_name like 'UNI-NA%'
order by support_group_name
for xml path('')) as Dummy(memberList)
go
The problem is when I call this via dbi and odbc it will always put 'None'
for operators even though if I do this in TOAD or MS Query it will pull the
correct values? I tried to get around this by making this a stored
procedure but the behavior is the same. Is there something I am missing? I
am calling this with the typical
cursor.execute(sample_query)
for row in cursor.fetchall()...
Any help would be appreciated.
Thanks,
--Chris
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
