We have an attribute that records our Friends (annual members) membership number and these all begin with a prefix of 16 for the next year. So numbers will range from 160000.
I've created a list to track these, pulling the attribute value for >=160000 and <=170000, however I'm getting 13 entries when I know there's currently only 3 real ones. I see no reason why the other accounts are falling into the list.
The attribute is used year after year so some accounts do have more than one entry of that attribute, though membership numbers wouldn't fall into this number range.
Any ideas what's happening?
Thanks,
What's your list criteria?
Correction: What are your list criteria?
Hi Rachel,
I've only got the criteria that pulls the membership number. So it's:
Annual member (Friend) >= 160000Annual member (Friend) <= 170000
Tim
Hi Tim
A couple of questions -
The attribute value field is actually a text field. That means that if you haven't explicitly defined your attribute as a "Number" Data Type in T_KEYWORD, it might be doing a text-based comparison instead of a numeric one, in which case, for example, your criteria would return numbers like 165 or 16234 as well as the ones you're looking for.
(And just as a general hint; if you're looking for a range, it's usually simpler and clearer to use "Between" 160000 and 170000 - makes a complex query easier to read, I think..)
Ken
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 again Tim,
Another option is to use the Like operator, since you know the exact number of digits. You can use Like with the value 16____ (16 with four single wildcard characters).
If you need 170000 to be included as well, you can do an = 170000 after the OR.
I think the problem with >= and <= is that they're testing for existence separately. So, for example, an account which had two instances of this attribute, one with key value 159999 and another with key value 170001, would be returned, because both the Exists statements would be true.