Home
|
Forums
|
Contact
|
Search
|
Syndication
[login]
[create account]
Friday, November 22, 2024
slxdeveloper.com
Home
Search
Contact us
About slxdeveloper
Syndication
Community
Forums
(NEW!)
Newsletter Archive
Members
Your Profile
Submit Article
General
Administration
(6)
OLE DB Provider
Miscellaneous
(2)
Architect
VBScript
(9)
ActiveX Controls
(6)
How To's
(14)
.NET Extensions
(3)
External
OLE DB Provider
(12)
SLAPI (SlgxApi.dll)
SalesLogix COM
(1)
Web
ASP/ASP.NET
(2)
Web Services
Web Client
Downloads
Samples
(17)
Documentation
(7)
Utilities
(18)
Resources
SalesLogix
(3)
Programming
(1)
11/22/2024 2:56:40 PM
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:
Dynamic
Flat
Tree
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?
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?
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?
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?
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?
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?
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?
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?
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!
c
[
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/22/2024 3:23:20 PM