The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Qry help for joining tables
Posted: 24 Sep 08 1:41 PM
|
I hope someone can help me with this. I have a qry that I can run in Query Analyzer but I cannot get it to run in Saleslogix. I keep getting a Failed to Parse error whenever I add that last "Left Join". I don't get the error if I leave off the "Left", but I do get it when I add it in. Also, when it does run, it should give me back 1 record for all of the data in the account_serviceagreem file with a column for contactname that is null. It isn't giving me any data. Can someone please look at this and tell me what I am doing wrong?
strSQL = "SELECT distinct s.*, (c.firstname + ' ' + c.lastname) as contactname, c.workphone "&_
"FROM account_serviceagreem s "&_
"LEFT OUTER JOIN account_misc a on s.parent_no = a.parent "&_
"LEFT JOIN contact_account_role r on a.accountid = r.account_id and r.contact_type = 'L' "&_
"LEFT JOIN contact c on c.accountid = r.account_id and c.contactid = r.contact_id "&_
"WHERE s.account_serviceagreemID = '"& strVal &"' and a.Parent = '"& strVal2 &"' and a.cmcust = '"& strVal2 &"' "
Thanks,
Renee
 |
|
|
|
Re: Qry help for joining tables
Posted: 25 Sep 08 7:07 AM
|
I think the problem is the extra 'where' in one of you joins. Try this:
strSQL = "SELECT distinct s.*, (c.firstname + ' ' + c.lastname) as contactname, c.workphone "&_
"FROM account_serviceagreem s "&_
"LEFT OUTER JOIN account_misc a on s.parent_no = a.parent "&_
"LEFT JOIN contact_account_role r on a.accountid = r.account_id "&_
"LEFT JOIN contact c on c.accountid = r.account_id and c.contactid = r.contact_id "&_
"WHERE r.contact_type = 'L' and s.account_serviceagreemID = '"& strVal &"' and a.Parent = '"& strVal2 &"' and a.cmcust = '"& strVal2 &"' "
|
|
|
|
Re: Qry help for joining tables
Posted: 25 Sep 08 8:15 AM
|
I tried what you suggested but I still received the same error. If I remove the "left" from the last join I don't get the error, but I am not getting the results that I want. I still want to get back the data from the account_serviceagreem table even if there is no contact with contact_type 'L' in the contact_account_role table. My sql only seems to return a record if there is a contact with 'L' type. Any other suggestions???
Thank you very much for your help!
Renee |
|
|
|
Re: Qry help for joining tables
Posted: 25 Sep 08 7:17 PM
|
Hi Renee,
Your original query appears to be fine. However, just double check that the values in your "WHERE" clause will actually return rows from the account_serviceagreem and account_misc tables. If they don't then the contact role not being "L" will not matter at all because you will get no rows.
I don't know your logic but the thing that looks suspicious to me is that you use the strVal2 field for 2 comparisons - is that right?
Stephen
|
|
|
|
Re: Qry help for joining tables
Posted: 26 Sep 08 9:22 AM
|
We've experienced similar issues where queries work fine in SQL QA but not through the Provider.
Is there a Provider issue here? |
|
|
|