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!
|
|
SQL Question
Posted: 03 Dec 07 9:26 AM
|
Is it possible to run the following query in the sql tool in the administrator? It fails as written in the SLX query tool, but works when run in MS SQL.
UPDATE sysdba.c_OPPORTUNITY_product SET SITEDESIGNATION = AC.SITEDESIGNATION
FROM sysdba.C_opportunity_product inner join sysdba.c_opportunity_product as AC on oppproductid = AC.oppproductid
WHERE oppproductid in('Q6UJ9A00LS8Q',blah blah)
Thanks
|
|
|
|
Re: SQL Question
Posted: 03 Dec 07 1:03 PM
|
| Try removing the keyword AS i.e. "sysdba.c_opportunity_product AC" I think it should work, although I have not tried it. |
|
|
|
Re: SQL Question
Posted: 03 Dec 07 5:19 PM
|
The SLX OLEDB provider does not appear to support UPDATE ... FROM queries. Frustrating, I know.
Also, your query is doing a self join on C_OPPORTUNITY_PRODUCT ... on its own ID field - this achieves nothing 
Try changing the syntax along the following lines for updating one table from another:
UPDATE C_OPPORTUNITY_PRODUCT
SET SITEDESIGNATION = (SELECT AC.SITEDESIGNATION FROM OTHER_TABLE AC where AC.ID = C_OPPORTUNITY_PRODUCT.OPPPRODUCTID)
By the way, no need to use the 'sysdba.' prefix when running queries in the Administrator client.
PP |
|
|
|
Re: SQL Question
Posted: 03 Dec 07 5:41 PM
|
| Phil, I am actually trying to update some records with themselves in hopes of creating some TEF's and getting some missing data in existing records to a remote office.. |
|
|
|
Re: SQL Question
Posted: 03 Dec 07 8:34 PM
|
Aha - that old problem.
I'm afraid that this idea won't work - all that will sync out to your remotes is the UPDATE statement, which will then execute on their versions of the db - the data itself will not sync out. |
|
|
|
Re: SQL Question
Posted: 04 Dec 07 6:43 AM
|
| Hmmm.. the records exist on the remote already - just need to update some fields that didn't sync.. Anyway to reconsile this? |
|
|
| |
|
Re: SQL Question
Posted: 04 Dec 07 9:35 AM
|
| I like the world you live in Carla - how do I sign up? How come nobody has developed a utlity to resync records from the host db? That would be quite useful.. There is always the bundle the record and reapply trick.. |
|
|
|
Re: SQL Question
Posted: 04 Dec 07 9:42 AM
|
By lottery. People used to queue but the line became too long and crowd control was a complete mess! (big grin)
OK, so the base rcord exists on the remote but the data you want to update said record, exists only on the Host? Is this correct? (Hence the Update won't work because the data does not exist on the remote)
c |
|
|
| |
|
Re: SQL Question
Posted: 04 Dec 07 9:53 AM
|
VIABLE OPTIONS?
1.) Is synching that table to the remote a possibility?
2.) Are manual Updates Possible? Ie.
UPDATE C_OPPORTUNITY_PRODUCT
SET SITEDESIGNATION = "MySiteA"
WHERE C_OPPORTUNITY_PRODUCT in ('MY FIRST 100 OPP_idS')
If #2 is viable, I will dig up a SQL script that will help you actually generate the Update statements so you don't have to type them individually. You can contact me offline if this is a possibility.
How many SITEDESIGNATIONs are we talking about? How many affected records? How many affected remotes?
|
|
|
|
Re: SQL Question
Posted: 04 Dec 07 4:19 PM
|
Hey, that sounds like an interesting script - if you end up finding it (or something similar) I would like to see it. Please 
PP |
|
|
|
Re: SQL Question
Posted: 05 Dec 07 7:14 AM
|
Originally posted by Steve Knowles
.... How come nobody has developed a utlity to resync records from the host db? That would be quite useful.. .. |
|
Actually.. I've been re-synching data to/from remotes/main db for some time.. was doing it "back in 5.2.x".
(being VERY carefull to not cross that "commercial line".. but will "bump it" 
We have the technology to do almost anything one wants to do in re-synching. The concept is fairly simple and has been discussed several times in news groups(s). However, the actual "tool" is something that we license out.. not sell or provide for free.
--
rjl |
|
|
|
Re: SQL Question
Posted: 05 Dec 07 8:08 AM
|
I ended up bundling the records and reinstalling. It works but it ain't pretty.
RJ, Are these discussions you mention found on SLXDeveloper.com? It seems that SLX would take notice and ship a tool that would improve one of their biggest weaknesses (in my opinion). Not that you shouldn't get paid for your initiatives, but anyone with remotes has had these type of problems. If you know where I can see these posts I would be very interested. Good to know you have the 'technology' should a sync crisis of epic proportions ever arise. |
|
|
|
Re: SQL Question
Posted: 05 Dec 07 3:34 PM
|
It works.. but it's not the best way... You really want to "traverse" the hierarchy.. do it based on certain filters, etc..
Don't look to Sage SalesLogix to implement this.. I've talked w/them about it (various times over the past 2/3 years).. Just don't see them doing it. "Tools" have not been their "thing".. never... Even talked about "selling" the tool to them.. no go.
So I'll "license" it's use on a "site by site" (system by system) basis... but not ever sell it outright to anyone or ever make it public domain. There's just too much time and effort invested in it.. and 'll probably not ever re-coup my investment. However, for those who are my clients.. the technology has proved to be invaluable. It's even got me a client or two over the years. So from that point of view.. it certainly works for me
Look at the Partner NG and do asearch.. there's random postings there.
--
rjl |
|
|
|
Re: SQL Question
Posted: 06 Dec 07 8:24 AM
|
Once upon a time - i.e. in legacy code, working for a different bp, etc...
We had just done a big conversion, and the guy on site forgot to check the history grooming features on remotes.
I created a routine to "republish" history that could be run on demand
Effectively, it reinserts existing records on the host.
For each record, you do the following
1. capture record information.
2. delete the record
3. insert the record using the captured info
As a quick & dirty, you could probably bang it out in a few hours.
It worked very well.
Hmmm.... maybe it SHOULD become a 3rd party utility
ws |
|
|
|