11/22/2024 9:55:45 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 T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Querybuilder/SQL Question
Posted: 05 Dec 07 4:19 PM
|
I am trying to build an account query that returns a set of accounts that includes in the layout the last history date (History.Completeddate) for any history items in the account where the history.type in(262154,262155,262145,262148,262146,262147) - where the codes correspond to certain history types. So the list would only show one account per record with the last history date for whichever history item from the list of types is the last completeddate.
My first thought is to do this with a query, although I am certainly open to other suggestions:
This returns almost what I want, but I can figure out how to get only the history record with the last history.completeddate into the query..
select a.accountid from sysdba.account a, sysdba.history h
where a.subtype='Specifier' and h.accountID=a.accountId and
h.type in(262154,262155,262145,262148,262146,262147)
.. then something like 'and h.Completeddate=MAX(h.Completeddate)' - but of course that doesn't fly.
Any thoughts, suggestions? Thanks
|
|
|
|
Re: Querybuilder/SQL Question
Posted: 05 Dec 07 4:57 PM
|
This gets most unpleasant, as the only way you can actually start typing some 'raw' SQL into a SLX group is through devious use of the 'IN' condition - this lets you get pretty clever about which accounts are included in a group
SELECT ...
WHERE ACCOUNTID IN (select rhubarb rhubarb)
but not when you want a fancy aggregate.
One suggestion, if your version of SLX supports it, is to build a view in pure SQL Server containing the stuff you want and then to use that in your group definition.
PP |
|
|
|
Re: Querybuilder/SQL Question
Posted: 06 Dec 07 11:01 AM
|
Well, here's something you can use:
SELECT AccountID, Max(HistoryID) LatestHistoryID FROM History WHERE Type IN (262154,262155,262145,262148,262146,262147)
GROUP BY AccountID
Create a view with that and you can use it in SalesLogix to pull the most recently created history ID for each account ID. This does make one assumption that isn't completely inline with what you mentioned - of course IDs are created in numerical order, which means that the highest ID was created most recently. The assumption is that the completed date isn't altered very often and lines up with the order of the IDs created. The reason for doing it the way I did it was to give you an ID of a history record with which to create a join.
Once you create a view using that SQL, you can use it one of two ways in SalesLogix: first is to join to it (if you have SLX version 7.0 or higher). Join from Account to the new view to the history table, and that should pull the most recent history record for each account. Second way is to just join from Account to History, and then in the conditions add "HistoryID IN (SELECT LatestHistoryID FROM MyNewView)" After doing either of those, you can add the other condition for the account: Account.Subtype = 'Specifier'.
One other note on this SQL code - for me it runs pretty slowly, probably because I don't have a proper index to support it - so if speed is an issue you may want to fix that as well. |
|
|
|
Re: Querybuilder/SQL Question
Posted: 06 Dec 07 4:57 PM
|
Hi Jeff - nice lateral thinking.
But I think that your assumption about the ascending HistoryIDs is invalid if there are remotes ...
I think you need to go back to something like this (very similar to your suggestion)
select accountID, Max(CompletedDate) LatestHistDate
from history
WHERE Type IN (262154,262155,262145,262148,262146,262147)
group by AccountID
and then do an inner join to history on AccountID = AccountID and LastestHistDate = CompletedDate
PP
|
|
|
|
Re: Querybuilder/SQL Question
Posted: 06 Dec 07 5:08 PM
|
| Good catch on the remotes - I didn't even think about that. And I guess the dates in SalesLogix are specific enough with the times that they would almost be unique enough as the IDs. |
|
|
|
Re: Querybuilder/SQL Question
Posted: 13 Jan 09 12:38 PM
|
I'm using 7.2 and cannot for the life of me figure out how to join a view in the querybuilder.
The view is all well and good, but I cannot see any way to add it into the SLX querybuilder (which is basically like oven mitts, in my opinion) - any help would be appreciated. |
|
|
|
Re: Querybuilder/SQL Question
Posted: 13 Jan 09 12:57 PM
|
Have you enabled the view first in the Database Manager?
If not, and if you aren't familiar with how to do this, here are the directions ---
Open the Architect, then open the Database Manager by going to Manage -> Database, identify the view you want to use on the left side (is should haev a grey symbol next to it), right click on it, click Properties, click the "Enable" button, then click OK - and that should do it - you'll probably want to restart SalesLogix just to make sure it shows up in the query builder.
If that wasn't it, please clarify - I'd be glad to help. |
|
|
|
Re: Querybuilder/SQL Question
Posted: 13 Jan 09 1:03 PM
|
Ha ha.
That's it exactly, I hadn't enabled it in the Database Manager - silly mistake. Thanks so much for the help, I was beating my head against the poor documentation our copy of SLX came with.  |
|
|
|
Re: Querybuilder/SQL Question
Posted: 13 Jan 09 2:20 PM
|
double edit:
Ok, we still can't see views in the Database Manager, only tables - any idea why? |
|
|
|
Re: Querybuilder/SQL Question
Posted: 13 Jan 09 2:36 PM
|
I just consulted my consultant, Jason McCormick at Lexnet, and this is what he said -
Find out the owner of the view - the view must be owned by Sysdba, otherwise it won't show up.
Other than that, I'm not sure why it wouldn't show up - at this point, I'd start checking to make sure I'm looking at the right database, looking for it in the correct spot in the DB manager, etc - checking everything.
Did that help at all? |
|
|
|
Re: Querybuilder/SQL Question
Posted: 14 Jan 09 11:27 AM
|
Nailed it on the head. Thanks Jeff.
Now that it's enabled, it should show up in the query builder, correct? |
|
|
|
Re: Querybuilder/SQL Question
Posted: 14 Jan 09 12:07 PM
|
Nice that the view db owner was the key point - I'll let the BP know he's brilliant 
As for Query Builder, your question is answered through the relationship and join discussion - those items that show up automatically show up because they have key entries in the join table. For example, if you are looking at the query builder for accounts, then there needs to be a join in the join manager with the Account table as the parent and the view as the child. The view will also be available if you create a join while using the Query Builder, and the view will be in the list of child tables.
Let me know if this doesn't make sense - basically, a join is required for the view to be visible. |
|
|
|
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!
|
|
|
|
|
|
|
|