Hi all,
I am fairly new to Tessitura and am also new to SQL. We run a customer report that used table dbo.tx_xref which apparently is no longer in use with V11. Therefore the report is populating some old/incorrect information. I was wondering if there is a function that replaces this table, or if I have to join T_Association & T_Affiliation?
Here is the query:
USE [impresario]
GO
/****** Object: UserDefinedFunction [dbo].[LFS_Assoc_name] Script Date: 07/25/2013 13:59:57 ******/
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[LFS_Assoc_name] (@customer_no int, @assoc_type int)
RETURNS varchar(255)
AS
/*****************************************************************************************************************
This will so need a v11 makeover.
*****************************************************************************************************************/
begin
declare @assoc_name varchar(255)
select @assoc_name = ''
select @assoc_name = @assoc_name + rtrim(a.assoc_name) + ','
from (select top 500 a.name+ ' '+ CASE n1n2_ind WHEN 1 then '(N1)' when 2 then '(N2)' when 3 then '(Both)' else null end as assoc_name
from dbo.tx_xref a
where a.customer_no = @customer_no
and a.type=@assoc_type
and (start_dt<getdate() or start_dt is null)
and (end_dt>getdate() or end_dt is null)) as a
If Datalength(@assoc_name) > 1 -- CWR 1/24/2006
select @assoc_name = replace(@assoc_name+'~', ',~', '')
RETURN @assoc_name
END
Lucy
Thank you so much for your response. I am afraid I could not quite completely comprehend the solution you mentioned. I have minimal experience in SQL. Would you be able to describe a solution in more detail?
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Lucie Spieler Sent: Thursday, July 25, 2013 3:03 PM To: Steven Jirjis Subject: RE: [Tessitura Shared Reports Forum] Replacement for dbo.tx_xref
There is a bridge view called VXSB_XREF that you might be able to use that will allow you to run your function, but you’re probably better off going to a rewrite. You are correct that you’d be looking at T_ASSOCIATION and T_AFFILIATION. You may also want to use at V_CUSTOMER_WITH_HOUSEHOLD joined with T_CUSTOMER (to get the cust_type).
Here’s the bridge view document from tessituranetwork.com:
http://www.tessituranetwork.com/network/Products/New%20Technology%20Initiative/~/media/Documentation/v11/Preparing_Customizations_for_Tessitura_V11.docx
Good luck!
Lucie
______________________________ Lucie Spieler IT Development and Training Manager FLORIDA GRAND opera
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the 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!