11/26/2024 2:24:38 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss general administration topics for SalesLogix (including LAN & remote topics). View the code of conduct for posting guidelines.
|
|
|
|
SLX Query Builder Removing Conditions on Execution
Posted: 10 May 10 5:28 PM
|
I have a custom table with accounting data. When I run the SQL statement from "View SQL" in the Query Builder, it's clear that the SQL is correct. I profile a refresh of the group and SLX is taking the correct query...
SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.TRANSTYPE A2_TRANSTYPE, A2.TXNDATE A2_TXNDATE, A2.REFNUMBER A2_REFNUMBER,
A2.PONUMBER A2_PONUMBER, A2.ISPAID A2_ISPAID, A1.ACCOUNT_UC
FROM sysdba.ACCOUNT A1
LEFT OUTER JOIN sysdba.C_QBTRANSHEADER A2 ON (A1.ACCOUNT=A2.COMPANYNAME)
WHERE (A2.TRANSTYPE='Invoice')
ORDER BY A1.ACCOUNT_UC ASC
And executing it as...
SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.TRANSTYPE A2_TRANSTYPE, A2.TXNDATE A2_TXNDATE, A2.REFNUMBER A2_REFNUMBER, A2.PONUMBER A2_PONUMBER,
A2.ISPAID A2_ISPAID, A1.ACCOUNT_UC,A1.SECCODEID SLXSECCODEID9
FROM sysdba.ACCOUNT A1
LEFT OUTER JOIN sysdba.C_QBTRANSHEADER A2 ON (A1.ACCOUNT=A2.COMPANYNAME)
WHERE A1.ACCOUNTID IN ('A6UJ9A00007C','A6UJ9A00006G',...,'A6UJ9A0000DF','A6UJ9A0000DF','A6UJ9A0000DF')
ORDER BY A1.ACCOUNT_UC ASC,A1.ACCOUNTID
Notice that the trans type condition is completely removed resulting in an incorrect dataset. I have no idea why it is doing that. I appreciate any ideas you may have! Thank you!
C. Burriss |
|
|
|
Re: SLX Query Builder Removing Conditions on Execution
Posted: 11 May 10 12:08 AM
|
Have you verified that you are executing the same group? And/or that the Group releases are correct?
Things to check:
- Check for Groups with the same name that aren't based on the Group that you updated.
- Look at the Modify date (after you update releases) to make sure that all associated releases are executed.
- When tracing the group, look for the query against the Plugin table (to get the group info) that is executed to get the PluginID of the group that actually executes.
|
|
|
| |
|
Re: SLX Query Builder Removing Conditions on Execution
Posted: 13 May 10 2:18 AM
|
| The fact that your SQL contains the sysdba prefix is unusual. When I do View SQL with standard groups, there is no prefix. If you check your other groups (eg All Accounts), does the prefix appear there? |
|
|
|
Re: SLX Query Builder Removing Conditions on Execution
Posted: 13 May 10 9:28 AM
|
Hi Phil,
That's an excellent observation but I'm afraid I've led you astray. I had ran the queries in SQL Server to confirm the resultset and must have failed to remove sysdba from the query when copying. I've compared an out-of-the-box group vs. a new custom group (doesn't work) and sysdba doesn't show up via "View SQL" or in the SLX Profiler. Thank you.
By the way, this is in relation to v7.5.2.
C. Burriss |
|
|
|
Re: SLX Query Builder Removing Conditions on Execution
Posted: 17 May 10 9:27 AM
|
Hi Chris
SLX is trying to optimise by building the group based on accountids rather than the actual condition (using server side cursers).
Try ticking the 'return distinct rows only' checkbox on the defaults tab of the query builder - that will change the cuser to client side and force the client to run the sql that you have written.
Good luck
nick |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|