Hi everyone,
I’m looking for a bit of SQL that will join the separate lines in TX_CUST_NOTES based on their serial order. I figured that, before making something up, there must be someone out there whose already put it together. Am I wrong?
Alternatively, I was poking around trying to find the stored procedure for the component of the Full Bio report that reports on Research Notes. Unfortunately, there doesn’t appear to be documentation on that report, so I haven’t had much luck. If you know the SP for that sub-report, I’m sure I could find the SQL there, too.
Thanks in advance,
Rey
--
A. Rey Pamatmat
Tessitura Manager
The Public Theater
425 Lafayette Street
New York, NY 10003
(212) 539-8739
rpamatmat@publictheater.org
It’s a little bit of RBAR as far as SQL optimization is concerned, but a scalar function called FS_GET_CUST_NOTES takes care of the serialization in AP_CUST_NOTES_BIO.
Here’s the code from it:
Select a.customer_no,
a.cust_notes_no,
a.note_type,
c.description,
a.create_dt,
a.last_update_dt,
a.created_by,
a.last_updated_by,
notes = [dbo].FS_GET_CUST_NOTES(a.cust_notes_no)
From [dbo].tx_cust_notes a
JOIN [dbo].vrs_cust_notes_type c on a.note_type = c.id
LEFT JOIN [dbo].FT_SPLIT_LIST(@note_types, ',') x on a.note_type = x.element
Where customer_no = @customer_no
and c.ok_to_print = 'Y'
and (@note_types is NULL or x.element > 0)
Order By c.description, a.last_update_dt desc
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of A. Rey PamatmatSent: Wednesday, December 01, 2010 12:15 PMTo: Ryan CrepsSubject: [Tessitura Technical Forum] Reporting on Research Notes and the Full Bio Report
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
An off-topic question..
Is FT_SPLIT_LIST more efficient than the "where charindex" method of dealing with multi-valued parameters?
It's an interesting question. I would have thought that the list function was more efficient and our earlier testing showed that it was, but I found some web code where the charindex method was noticeably faster. I think it depends where you are using it and I think it's in a WHERE clause that charindex is the winner. Which makes some sense because the optimizer spends a lot of time evaluating WHERE clauses and probably doesn't normally like to see function calls there. But as they always say, results may vary.