[DB-SIG] Question on odbc with cross apply and for xml...

[James Briggs]

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 <royhobbsx42 at yahoo.com> 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 at python.org
> http://mail.python.org/mailman/listinfo/db-sig
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20061128/b393f650/attachment.htm