• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 37 views
  • Users 0 members are here
Related

List builder query join issue

Matt Winchester
$organization

In list builder, if I select more that one item that originates from the same table with the same column information, list builder creates an "AND" in where clause which is fine. However, this returns 0 records. So my question is, where can I modify how the aliases are assigned when the SQL code is created? I see RP_BUILD_QUERY but that seems to be just for Extractions.

Here is what I get from list builder:

Select Distinct a.customer_no
 From t_customer a (NOLOCK)
JOIN lv_const_cust_current e (NOLOCK) ON a.customer_no = e.customer_no
JOIN TX_CUST_KEYWORD f (NOLOCK) ON a.customer_no = f.customer_no
 Where  IsNull(a.inactive, 1) = 1
 AND e.constituency in  (35)
 AND f.key_value in  ('A')  AND f.keyword_no = 403
 AND f.key_value in  ('Individual')  AND f.keyword_no = 402

 

Here is what I need:

Select Distinct a.customer_no
 From t_customer a (NOLOCK)
JOIN lv_const_cust_current e (NOLOCK) ON a.customer_no = e.customer_no
JOIN TX_CUST_KEYWORD f (NOLOCK) ON a.customer_no = f.customer_no
JOIN TX_CUST_KEYWORD g (NOLOCK) ON a.customer_no = g.customer_no
 Where  IsNull(a.inactive, 1) = 1
 AND e.constituency in  (35)
 AND f.key_value in  ('A')  AND f.keyword_no = 403
 AND g.key_value in  ('Individual')  AND g.keyword_no = 402

 

Thanks in advance.

 

Parents
  • $organization

    Hi Matt,

     

    You can’t change how criteria sets convert the graphical interface into SQL code, but you can edit the SQL code after it’s created.  Just click the Manual Edit button at the bottom right of the criteria set.  Then you can paste in the code you want.

     

    I suspect the cause of the result you are getting is that you have the IN operator selected instead of the HAS operator.  If that’s the case, change to HAS for both attribute criteria and I believe your query will work as automatically generated.

     

    For more details on manual edit mode and IN and HAS see the Criteria Sets document.

     

    Kevin Sheehan

    Documentation & Learning Resources Specialist

    Tessitura Network

    +1 888 643 5778 x 329

    ksheehan@tessituranetwork.com

     

Reply
  • $organization

    Hi Matt,

     

    You can’t change how criteria sets convert the graphical interface into SQL code, but you can edit the SQL code after it’s created.  Just click the Manual Edit button at the bottom right of the criteria set.  Then you can paste in the code you want.

     

    I suspect the cause of the result you are getting is that you have the IN operator selected instead of the HAS operator.  If that’s the case, change to HAS for both attribute criteria and I believe your query will work as automatically generated.

     

    For more details on manual edit mode and IN and HAS see the Criteria Sets document.

     

    Kevin Sheehan

    Documentation & Learning Resources Specialist

    Tessitura Network

    +1 888 643 5778 x 329

    ksheehan@tessituranetwork.com

     

Children