What I am trying to do is write a query that will list all SEI processes and the Folders they are located in. I can't seem to find any table in the SEI database that contains the folder structure. Is what I'm trying to do possible? My organization is…
You may have a business requirement to cascade a change through the child records of a company. I have discussed previously how a table level script can be used to update all opportunities if the company is reassigned to a new account manager. See the…
I wrote a SQL query to evenly distribute leads to the user. The query runs perfectly on SQL Server, but not Sage CRM 7.1SP2. Any feedback is welcome! Thanks!
The query is below. Here is what I did ,and got the SQL errors. The expected result is when…
I have a list of email addresses that were uploaded against the wrong Person records and am trying to run a query updating the emaillink ELink_RecordID column with the correct person IDs, but the query fails consistently.
Any idea why the update fails…
I am trying to use subqueries and joins in my PHP script that is pulling data from SAGE, but I keep getting this error: Warning : odbc_exec(): SQL error: [ProvideX][ODBC Driver]Expected lexical element not found: <identifier>
Here is my query:
SELECT…
Hi,
I am trying to run a query to pull fields from the screen Miscellaneous Receipts. The three things I need to pull are Quantity/Product/Receipt Date/Supplier Lot Number. I looked at the screen level and did not find a table from which I can pull…
Hi Israel,
I have yet another SQL question (sorry, we don't have a Crystal reports integration!). I want to use a subquery to extract the Average cost of each product by using the AVG aggregate, but I am unsuccessful in doing so. What I have so far…
Does anyone know how to join tables through code (with one table being ATABDIV (misc tables))? I am trying to link TSICOD (statistical group ) (dim=0) to the table on the ATABDIV table (it's table 20), but I can't do it through code. Can anyone help me…
Hi Bob
Thank you for the response. I used your outlined solution using Declare and Set operators, with the result:
DECLARE S.USRFLD1_0 VARCHAR(20)
SET S.USRFLD1_0 = '01/01/2010'
SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0, S.LOT_0, S.STA_0, S.USRFLD1_0…
Hello,
I have created the following query, but am getting an annoying message saying "the conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value." (see below screenshot).
SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0…
Hello,
I am new to the world of Sage ERP.
I have designed this query and it works fine in SQL:
SELECT o1.*, ( Select isnull(SUM(D002_DollarsRented)+SUM(DollarsSold),0) FROM openquery(TESTSWRMAS, 'select * from AR_CustomerSalesHistory') o2 WHERE…
Well, this has proven to be a bit of a wild goose chase so far!
I've had a few offline discussions with Richard Ridings as well, and I now have a query that works fine (thanks Richard!) - as long as it is run manually. I can run all three - manually…
I would like to construct an SQL query, which dumps to Excel, an analysis of the job costing for a range of jobs across three different organizations. All have virtually identical structures and naming conventions. There is not a ton of data to grab,…
