9/13/2025 10:28:46 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 using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
Update based on other table fails under 7.2
Posted: 30 Jun 08 4:43 PM
|
I am converting a VB6 program which runs succesfully under 6.1 but fails with parse error under 7.2.
under 7.2, strsql = "update C_ACC_SELLOUTEXT set t01=1" works but:
strsql = "update sysdba.C_ACC_SELLOUTEXT set t01=sysdba.C_ACC_SELLOUT.invoicevalue, " & _
"c01=sysdba.C_ACC_SELLOUT.costvalue, u01=sysdba.C_ACC_SELLOUT.quantity" & _
" FROM sysdba.C_ACC_SELLOUTEXT, sysdba.C_ACC_SELLOUT " & _
"where month (sysdba.C_ACC_SELLOUT.invoicedate)=1" & _
" and sysdba.C_ACC_SELLOUT.C_ACC_SELLOUTID = sysdba.C_ACC_SELLOUTEXT.C_ACC_SELLOUTID"
fails with Error: -2147211398 Failed to parse SQL
The same code works under 6.1.
In both cases I use the SalesLogix OLEDB provider . Is this a problem in the 7.2 provider? |
|
|
|
Re: Update based on other table fails under 7.2
Posted: 01 Jul 08 6:41 AM
|
Cannot do an "Update... from..."
You have to use the "sub-select approach.
EX:
Update Table1 Set Field1 =
(Select Field2 From Table2 Where Table1.KeyField = Table2.KeyField)
..another
--HowTo - Update from another table using SubSelects
UPDATE sysdba.CONTACT
SET ACCOUNT = (SELECT a.ACCOUNT
FROM ACCOUNT a
WHERE CONTACT.ACCOUNT = a.ACCOUNTID)
WHERE ISNULL(CONTACTID, '') = '' AND
USERFIELD10 <> '' AND
EXISTS (SELECT *
FROM ACCOUNT a
WHERE CONTACT.ACCOUNTID = a.ACCOUNTID)
--
RJLedger - rjlSystems
"...Customer First..."
|
|
|
|
Re: Update based on other table fails under 7.2
Posted: 02 Jul 08 7:13 AM
|
Bob
Thanks very much for the help. It worked.
However, it does have a weakness in that trying to update multiple fields in the same statement is a problem. In my case, I was able to get round it by modifying an earlier statement to push all the data needed for the query into the same table, which blew my normalization, but at least got me over the problem. |
|
|
|
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!
|
|
|
|
|
|
|
|