List builder query join issue

Hi Matt, I usually do this inside the list builder directly by clicking on the Display Query and then Manual Edit button. Not sure if you are asking for this though?

Mo

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.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

Hi Matt, I usually do this inside the list builder directly by clicking on the Display Query and then Manual Edit button. Not sure if you are asking for this though?

Mo

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.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!