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!
|
|
UNION in Group Tab
Posted: 15 Jun 06 6:40 AM
|
fiogf49gjkf0d We are running SLX 6.2.1 and I just tried creating a group tab that used a UNION in the select statement. Basically it was this...
SELECT accountid FROM opportunity WHERE accountid IS NOT NULL AND accountid > '' AND closeprobability BETWEEN 1 AND 99
UNION
SELECT accountid FROM activity WHERE accountid IS NOT NULL AND accountid > ''
Apparenlty the SLX SQL parser doesn't like UNIONs because I received an error when I saved the group. So I had to split the UNION into 2 different SELECTs and two different conditions in the group. This makes the group conditions a little harder to manage but it works.
Are UNIONs handled in later versions? Maybe 7?
Thanks,
John G. |
|
|
|
Re: UNION in Group Tab
Posted: 15 Jun 06 7:49 AM
|
fiogf49gjkf0d Try this syntax.
SELECT a1.accountid FROM opportunity a1
WHERE a1.accountid IS NOT NULL AND a1.accountid > ''
AND a1.closeprobability BETWEEN 1 AND 99
UNION
SELECT a2.accountid FROM activity a2
WHERE a2.accountid IS NOT NULL AND a2.accountid > '' |
|
|
|
Re: UNION in Group Tab
Posted: 15 Jun 06 8:04 AM
|
fiogf49gjkf0d Nope. Just tried that and it does not work. I viewed the SQL and this is what it showed
SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.MAINPHONE, A1.TYPE, A1.SUBTYPE, A1.STATUS, A1.ACCOUNTMANAGERID, A1.SECCODEID, A1.ACCOUNT_UC FROM ACCOUNT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) WHERE (A2.COUNTY LIKE 'Camden%') AND (A2.STATE IN ('N.J.','NJ')) AND (A1.ACCOUNTID IN (SELECT a1.accountid FROM opportunity a1 WHERE a1.accountid IS NOT NULL AND a1.accountid > '' AND a1.closeprobability BETWEEN 1 AND 99 UNION SELECT a2.accountid FROM activity a2 WHERE a2.accountid IS NOT NULL AND a2.accountid > '')) ORDER BY A1.ACCOUNT_UC ASC
I noticed a1 is already being used so I changed it to read
SELECT b1.accountid
FROM opportunity b1
WHERE b1.accountid IS NOT NULL AND b1.accountid > '' AND b1.closeprobability BETWEEN 1 AND 99
UNION
SELECT b2.accountid
FROM activity b2
WHERE b2.accountid IS NOT NULL AND b2.accountid > ''
Figuring the aliases were conflicting but the result was the same.
Just seems like UNION does not work.
John G. |
|
|
|
Re: UNION in Group Tab
Posted: 15 Jun 06 9:09 AM
|
fiogf49gjkf0d Looks like you are right. I used ADO Explorer to issue the statement directly against the SQL database and it worked. I then issued the statement through the SLX OLEDB Provider and it failed.
Have you contacted SalesLogix? |
|
|
|
Re: UNION in Group Tab
Posted: 15 Jun 06 9:34 AM
|
fiogf49gjkf0d No, haven't contacted SLX as of yet. Will do so today.
John G. |
|
|
|
Re: UNION in Group Tab
Posted: 15 Jun 06 10:23 AM
|
naltrexone where to buy buy naltrexone online canada click here fiogf49gjkf0d Contacted SLX support and confirmed this is a defect in 6.x (UNIONs in subquery not working) and is slated to be fixed in 7.0.
John G. |
|
|
|