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!

Architect Forums - SalesLogix Scripting & Customization
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to SalesLogix Scripting & Customization | New Thread

View: Search:

Author

Thread: Using Partition by in a SQL statement in a script

Maria Amodei
Posts: 24

Using Partition by in a SQL statement in a script Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 30 Oct 07 12:07 PM

I'm using (trying to use) a SQL statement to return a recordset. The statement includes a partition by subtotal as one of the columns. I'm working against SQL 2005. The statement works fine directly against MSSQL, but fails with "unable to parse sql" when run from my script in SLX. Is partition by not supported?

Maria

[Reply][Quote]

Phil Parkin
Posts: 819

Re: Using Partition by in a SQL statement in a script Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 30 Oct 07 4:06 PM

I haven't checked specifically, but probably not. The SLX OLEDB provider implements standard ANSI SQL circa 1952 (just kidding).

So you'll need to find another way: if you're just returning a read-only recordset, you have several options. One is to create a direct (SQL) connection in code and execute the query through that (in the knowledge that you are bypassing any user security). Alternatively, maybe you can create a view in SS2005 which already contains the subtotals and SELECT from that?

PP

[Reply][Quote]

Maria Amodei
Posts: 24

Re: Using Partition by in a SQL statement in a script Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 31 Oct 07 9:46 AM

Maybe 1960?

In a burst of genius I remembered this client has Sql 2000 anyway so I've found another way to manage my recordset. I was using a simple partition by to count the number of records that matched a certain criteria. Then when I looped through the record set I could increment and know when I'd reached the end of each like set. It was so easy it had to fail!

Maria

[Reply][Quote]

Page 1 of 1

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!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 6/20/2025 7:25:28 PM