Attribute List inconsistency

Hi Tim,

SQL can do a compare of alphanumeric fields using BETWEEN.  For example, you could: select * from t_customer where lname between ‘A’ and ‘B’ and you will get all customers whose last names begin with ‘A’.  However, it appears that List Manager/Extractions in Tessitura have the Between function programmed to only look at numeric and date fields.   So I think you could do this in 2 ways:

Option 1:             You could create a view for your attribute where you define it as numeric and then between would work.

Something along the lines of:     Select customer_no,keyword_no,convert(int,key_value) as membership_no from tx_cust_keyword where keyword_no = 334 and

Then create an entry in T_KEYWORD for membership_no.   You would probably need to put some logic in there to account for your older data that had alpha characters – to strip those out; so there wouldn’t be any issues.

Option 2:             You could write custom SQL to do it – along the lines of

                Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

                                Join TX_CUST_KEYWORD ck with (nolock) on a.customer_no = ck.customer_no

                                Where  IsNull(a.inactive, 1) = 1 and ck.keyword_no = 334 and ck.key_value between ‘160000’ and ‘170000’

Good luck,

Lisa

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tim Chandler
Sent: Friday, July 24, 2015 12:41 AM
To: llindvall@cfl.rr.com
Subject: Re: [Tessitura Technical Forum] Attribute List inconsistency

Cheers Ken.

The attribute won't allow me to use the Between function. It's still set up for alphanumeric entries. I think I'm going to need to retire this attribute and create a new one to better report on things moving forward. Sorry, am thinking out loud here. It's used to record how many times someone has been a member as the Friends are technically separate from the Festival and membership payments are not processed through Tessitura. 

The query is:

Select Distinct a.customer_no 

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

 Where  IsNull(a.inactive, 1) = 1 

 AND EXISTS (select * from TX_CUST_KEYWORD WITH (NOLOCK) where TX_CUST_KEYWORD.customer_no in (select  customer_no from V_CUSTOMER_WITH_PRIMARY_GROUP where customer_no = a.customer_no) and TX_CUST_KEYWORD.key_value >= '160000' And TX_CUST_KEYWORD.keyword_no = 334) 

 AND EXISTS (select * from TX_CUST_KEYWORD WITH (NOLOCK) where TX_CUST_KEYWORD.customer_no in (select  customer_no from V_CUSTOMER_WITH_PRIMARY_GROUP where customer_no = a.customer_no) and TX_CUST_KEYWORD.key_value <= '170000' And TX_CUST_KEYWORD.keyword_no = 334) 

The other 10 accounts have multiple entries though some of them do have older membership numbers including 'a12345' and '10.1234' formats. I'm assuming this is throwing it out?

Hi Tim,

SQL can do a compare of alphanumeric fields using BETWEEN.  For example, you could: select * from t_customer where lname between ‘A’ and ‘B’ and you will get all customers whose last names begin with ‘A’.  However, it appears that List Manager/Extractions in Tessitura have the Between function programmed to only look at numeric and date fields.   So I think you could do this in 2 ways:

Option 1:             You could create a view for your attribute where you define it as numeric and then between would work.

Something along the lines of:     Select customer_no,keyword_no,convert(int,key_value) as membership_no from tx_cust_keyword where keyword_no = 334 and

Then create an entry in T_KEYWORD for membership_no.   You would probably need to put some logic in there to account for your older data that had alpha characters – to strip those out; so there wouldn’t be any issues.

Option 2:             You could write custom SQL to do it – along the lines of

                Select Distinct a.customer_no From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

                                Join TX_CUST_KEYWORD ck with (nolock) on a.customer_no = ck.customer_no

                                Where  IsNull(a.inactive, 1) = 1 and ck.keyword_no = 334 and ck.key_value between ‘160000’ and ‘170000’

Good luck,

Lisa

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tim Chandler
Sent: Friday, July 24, 2015 12:41 AM
To: llindvall@cfl.rr.com
Subject: Re: [Tessitura Technical Forum] Attribute List inconsistency

Cheers Ken.

The attribute won't allow me to use the Between function. It's still set up for alphanumeric entries. I think I'm going to need to retire this attribute and create a new one to better report on things moving forward. Sorry, am thinking out loud here. It's used to record how many times someone has been a member as the Friends are technically separate from the Festival and membership payments are not processed through Tessitura. 

The query is:

Select Distinct a.customer_no 

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

 Where  IsNull(a.inactive, 1) = 1 

 AND EXISTS (select * from TX_CUST_KEYWORD WITH (NOLOCK) where TX_CUST_KEYWORD.customer_no in (select  customer_no from V_CUSTOMER_WITH_PRIMARY_GROUP where customer_no = a.customer_no) and TX_CUST_KEYWORD.key_value >= '160000' And TX_CUST_KEYWORD.keyword_no = 334) 

 AND EXISTS (select * from TX_CUST_KEYWORD WITH (NOLOCK) where TX_CUST_KEYWORD.customer_no in (select  customer_no from V_CUSTOMER_WITH_PRIMARY_GROUP where customer_no = a.customer_no) and TX_CUST_KEYWORD.key_value <= '170000' And TX_CUST_KEYWORD.keyword_no = 334) 

The other 10 accounts have multiple entries though some of them do have older membership numbers including 'a12345' and '10.1234' formats. I'm assuming this is throwing it out?