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
Thanks in advance btw!
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 SpielerIT Development and Training ManagerFLORIDA GRAND opera
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
______________________________ 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!
The view we use throughout the system for combining associations and affiliations is VS_RELATIONSHIP, which also takes care of control group security.
+Ryan Creps
+Tessitura Network
From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Steven Jirjis Sent: Thursday, July 25, 2013 10:04 PM To: Ryan Creps Subject: RE: [Tessitura Shared Reports Forum] Replacement for dbo.tx_xref
I'm looking at the T_keyword setup for Relationship End Date and Relationship Start Date. And seem to be having some problems with the way the start and end dates are working. My sense is that this may be related to all of the nulls that are usually in the system.
I'm wondering about updating the Detail Col in T_Keyword to read:
isnull(!.end_dt,'9999-12-31 23:59:59.997')
and
isnull(!.start_dt, '1753-01-01 00:00:00.000')