11/25/2024 5:22:43 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!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Subquery
Posted: 16 Aug 10 11:54 AM
|
| Why doesn't Saleslogix support Subquery? |
|
|
|
Re: Subquery
Posted: 16 Aug 10 12:44 PM
|
Per se I have a simple select statement
Select max(p.name)
From
(Select ID, count(name)
FROM
Table1
group by ID
)
How can I solve it without the usage of Subquery??? I agree subquery should be avoided but it can be used in such situations. And to top it all...if I have a subquery, saleslogix cannot handle it and it gives an access violation error message and puts in a loop and causes the task manager to hang up and the only way to kill the process is to RESTART my machine................................. |
|
|
|
Re: Subquery
Posted: 16 Aug 10 12:55 PM
|
Not sure what the Actual official explanation is, but it has to deal with how SLX Parses the statement, and how it may add complexity to the process (and probably break it).
Now, regarding your actual SQL Statement, you could try similar to:
SELECT TOP 1 ID, COUNT(NAME)
FROM TABLE1
GROUP BY ID
ORDER COUNT(NAME) DESC
(I haven't tested this statement, so there may be issues with it, and it may still not run in SLX, but it is just a quick rewrite of your Query without using a SubQuery) |
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:21 PM
|
Thanks Raul for quick reply. But the sql statement you mentioned wouldn't work because, first we try to group by ID and then select the top 1 of the entire returned row and not per group.
I do have a complex SQL almost on the same lines. I guess at this point of time, I should look into using a Stored Procedure instead of inline SQL. Any thoughts/warning i shouldn't use a SP ? Also, any forum that describes best on how to use a SP within saleslogix ????? |
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:26 PM
|
Originally posted by Amita Jain
Per se I have a simple select statement
Select max(p.name)
From
(Select ID, count(name)
FROM
Table1
group by ID
)
How can I solve it without the usage of Subquery??? I agree subquery should be avoided but it can be used in such situations. And to top it all...if I have a subquery, saleslogix cannot handle it and it gives an access violation error message and puts in a loop and causes the task manager to hang up and the only way to kill the process is to RESTART my machine................................. |
|
First off, you are using a alias (p) which you have not defined.
Second, there is no column called 'Name' in your subquery.
Third, I cannot understand what you are actually trying to achieve - can you explain? |
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:37 PM
|
Originally posted by Phil Parkin
First off, you are using a alias (p) which you have not defined.
Second, there is no column called 'Name' in your subquery.
Third, I cannot understand what you are actually trying to achieve - can you explain? |
|
|
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:40 PM
|
You can't execute a Store Procedured within an SLX Connection, but there are several articles on this board on how to do that by opening a direct connection to the Underlying Database, and then executing the Procedures, that may help depending on what you are trying to do.
Also, regarding the SQL Statement, it does work. SQL would first do the Grouping (by ID) and then Order it. I did test it against some data sets on my Servers and it works properly.
Give it a try it works, this is what I tried:
SELECT STATE, COUNT(STATE)
FROM ADDRESS
GROUP BY STATE
ORDER BY COUNT(STATE) DESC
TX 1061
CA 704
MA 361
...
...
Then, add "Top 1" to the Query, and you will notice that it will give you just the first row from the Query:
SELECT TOP 1 STATE, COUNT(STATE)
FROM ADDRESS
GROUP BY STATE
ORDER BY COUNT(STATE) DESC
On my DB, that returns just one row:
TX 1061
|
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:51 PM
|
Originally posted by Phil Parkin
First off, you are using a alias (p) which you have not defined.
Second, there is no column called 'Name' in your subquery.
Third, I cannot understand what you are actually trying to achieve - can you explain? |
|
#1 - Yes, my bad I didn't type alias(p) in a hurry.
Select max(p.name)
From
(Select ID, count(name)
FROM
Table1
group by ID
) AS P
#2 - No column called "Name" in my subquery????
#3- My question is why can't we use subquery???
Another example
Select t1.firstname, t1.lastname, max(t1.Hits) as Hits
(Select firstname, lastname , classid, count(classid) as Hits
From table1
group by firstname, lastname , classid
) AS t1
group by t1.firstname, t1.lastname
When I execute I would get something like this.......(NOT AN ACTUAL SQL RESULTS)
Select firstname, lastname , classid, count(classid) as Hits
From table1
group by firstname, lastname , classid
I get
Firstname LastName classID Hits
--------------------------------------------------------------
ABC XYZ 1 3
ABC XYZ 2 1
My end result should be - trying to get maximum hits based on a person
FirstName LastName Hits
-------------------------------------------------
ABC XYZ 3
Able to do all using subquery in SQL Management studio...... |
|
|
|
Re: Subquery
Posted: 16 Aug 10 1:56 PM
|
FirstName LastName ClassID Hits UserID
-----------------------------------------------------------------------------------------------------------------------
ABC XYZ 1 3 1
ABC XYZ 2 1 1
TTT AAA 3 2 3
Final Result
-----------------------------
FirstName LastName Hits UserID
------------------------------------------------------------------------------------------------
ABC XYZ 3 1
TTT AAA 2 3 |
|
|
|
Re: Subquery
Posted: 16 Aug 10 2:31 PM
|
#2 - No column called "Name" in my subquery????
Just because you've got Select Count(Name) in your subquery does not mean that a column called 'Name' will be available from it. In fact, it won't.
The SLX OLEDB Provider uses an old version of SQL and some of the more modern constructs are not supported. That does not mean zero subqueries, but you have to know what will work.
Perhaps if you posted the actual SQL that works in SSMS someone will be able to help you. Your examples are somewhat confusing to me. |
|
|
|
Re: Subquery
Posted: 18 Aug 10 4:55 PM
|
Use a SQL Server Native connection and avoid all of this.....
why are you going through the Provider? |
|
|
|
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!
|
|
|
|
|
|
|
|