Hi Everyone, I am new to Tessitura and we converted a bunch of Named Spaces that Constituents gave to during a Campaign to Constituent Attributes. In LIST I see the Constituent Attribute as a selection criteria, but I am unsure how to use LIKE to say IS NOT NULL
The query looks like this but I need to figure out how to get TX_CUST_KEYWORD.key_value LIKE '0*' to be IS NOT NULL or whatever the equivalent is.
The data I am trying to retrieve looks like
G. Mt Vernon Barn Company Cupola
or
05. HAMMOCK LOUNGE
SELECT DISTINCT a.customer_nolFROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)WHERE a.inactive = 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 LIKE '0*' AND TX_CUST_KEYWORD.keyword_no = 470) 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 LIKE '*' AND TX_CUST_KEYWORD.keyword_no = 472)
Thank you for your help
I mean, I think you're there: if you change LIKE '*' to IS NOT NULL I think that should work. Since you only care about them having the Attribute, technically you could get rid of that whole clause and just have the AND TX_CUST_KEYWORD.keyword_no = 472.
Oh, oh, right: wildcards are different in SQL. "%" functions the way that "*" does pretty much everywhere else.
Just noting % is also the wild card for constituent search, and depending on what you're trying to actually accomplish, constituent search might possibly fill the need.
I am trying to get a list of all the constituents, the naming opportunities and their email address
They do not have something that is the same in common on all of them - like they all don't start with or contain a certain letter or have the same number of characters. I feel like I am missing something easy, but I can't figure out what.
I think what you want is a list of everyone with the keyword number and an output element with the value of that keyword number for the constituents who have a value. Here's recipes if you don't already have such . www.tessituranetwork.com/.../Tessitura.htm. https://www.tessituranetwork.com/Help_System/Tessitura.htm#Recipe%20Books/Output%20Sets/Emails.htm
I have the element in TR_Query_Element I just don't know how to Select only Constituents that Have something in the Element A% is everything that starts with A, I don't want to have to go through every iteration of letters and numbers and cob together one by one into a list. Is there a wildcard that says Starts with or contains any letter or any number?
Gawain's answer is correct on that. "Since you only care about them having the Attribute, technically you could get rid of that whole clause and just have the AND TX_CUST_KEYWORD.keyword_no = 472" UNLESS you're storing unrelated values in the same keyword number. In that case, you have to find something they all have in common that the unrelated values don't have.
Yay! It worked - I didn't realize we could use the Query function in LIST and delete criteria. Thank you! Thank You!