Reporting on Research Notes and the Full Bio Report

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

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

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!

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!

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

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

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!

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!