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!

Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to T-SQL & Queries | New Thread

View: Search:

Author

Thread: Subquery is not supported - Do I have to develop using VisualStudio for this?

RJ Palombo
Posts: 43

Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 20 Dec 07 11:26 AM

I'm trying to create a view that has predefined groups for our accountmanagers that I can seem to build in the normal list view.

Here is the SQL statement that I'm trying to run:

SELECT
C.Account, C.firstname, C.lastname, S.Total, A.State, AC.StartDate
From
sysdba.Contact C LEFT OUTER JOIN
sysdba.Address A
ON
A.AddressId = c.AddressId
LEFT OUTER JOIN
(select contactid, sum(Total) as Total From sysdba.PP_Sales Group by contactid) S
ON
S.Contactid = C.contactid
LEFT OUTER JOIN
(select contactid, max(StartDate) as startdate From sysdba.Activity Group by contactid) AC
ON
AC.contactid = C.contactid

Do I have to use VS.Net nad figure out how to do this through there or is there another way without using scripts that I can pull this data. basically I need to have a row coming from 4 different tables where two of the columes sumarize data or sort the data. Can anyone take a look at my statement and point me in the right direction. I feel like I'm going about this all wrong.

[Reply][Quote]

RJ Palombo
Posts: 43

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 20 Dec 07 11:45 AM

Our Sales Manager wants an easy way for the reps to pull uptheir customers with sales totals from the customer and other information easily and export it into Excel in a formated sheet. I figured the best way to do this (being the groups don't exactly get everything you want) is to create my own view that looks just like a list view and base it od SQL scripts that work in Query Analizer. This way I could copy the export to excel script and modify it the way I see fit and just bypass the original list view.

Is my only hope Crystal Reports or VS.Net?

[Reply][Quote]

RJ Palombo
Posts: 43

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 20 Dec 07 2:03 PM

I'm starting to use Crystal, but I realized that the reps wont be able to simply double click the contacts record and go to the contact.. Ugh, this is getting ridiculous.

[Reply][Quote]

Carla Tillman
Posts: 290

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 21 Dec 07 9:45 AM

If you are on a later version of SLX, you may create this Query as a SQL View then base your SLX Group off of that.

Carla

[Reply][Quote]

Jim
Posts: 17

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Jan 09 4:17 PM

You can also get around the SLX Query Builder's lack of subquery support with a little trick.
You can't do it through joins, but you can subquery by selecting a field and using an in statement, the entire in statement can be your subquery.

For example, here's something I did recently:
A3.ACCOUNTID IN (SELECT distinct ACCOUNTID FROM SYSDBA.C_MACHINES WHERE PRODUCTCODE IN ('XXXX'))

variations on this will take you rather far.

[Reply][Quote]

Walter Shpuntoff
Posts: 167

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 07 Jan 09 5:50 AM

How about a different approach entirely....

1. create a mainview & give it some catchy name (kind of like dashboard)
2. put a big empty grid control on it, maybe some additional fields to use for filtering, and set default values when it's opened to keep the size of the dataset reasonable.
3. build the grid from code, filling it with your values here.
4. use the export-grid-to-excel function if they really want to kill some trees or share it with their friends.

If you do a view like you are planning and your database gets big.... it will be unusable due to performance issues. (Yes.. I have those scars!)

ws

[Reply][Quote]

Jim
Posts: 17

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 13 Jan 09 12:06 PM

Quote:

Originally posted by Carla Tillman

If you are on a later version of SLX, you may create this Query as a SQL View then base your SLX Group off of that.

Carla

Carla, how do you go about doing this? I've created my view but I can't figure out how to set an SLX Group to use it as the base...
Thanks,
jim

[Reply][Quote]

Carla Tillman
Posts: 290

Re: Subquery is not supported - Do I have to develop using VisualStudio for this? Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 15 Jan 09 8:40 AM

Hi Jim,

You would need to create the view in SQL and then enable it in Database Manager tool.
Fun times!

[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 © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 11/26/2024 3:18:02 AM