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!
|
|
Execute SQL Problem
Posted: 01 Jun 06 3:30 PM
|
fiogf49gjkf0d I am trying to execute a SQL statement on the host and all of my remote databases, so I am using the execute SQL feature in Workgroup Admin, but it is kicking my statement out. This is SLX v.5.2.
Here is the statement:
update sysdba.contact set account = 'Stuart Securities Corp (Hub)' where account = 'W.H. Stuart & Associates (Hub)'
and here is the Error:
The Statement failed and returned the following error. Subquery returned more than 1 value and that is not permitted when subquery follows =, !=, <, <=, >, >= or when the subquery "
Any ideas?
I changes the query to search for contactid in (Select contactid from contact where account = 'W.H. Stuart & Associates (Hub)') but that didn't work either.
I'm going out of my mind!!
|
|
|
|
Re: Execute SQL Problem
Posted: 01 Jun 06 4:44 PM
|
pillola cialis controindicazioni acquistare cialis con paypal fiogf49gjkf0d You are working from the conact level. Account to Contact is a 1 to many
update sysdba.contact set account = 'Stuart Securities Corp (Hub)' where account = 'W.H. Stuart & Associates (Hub)'
Try this
update sysdba.contact
set account = 'Stuart Securities Corp (Hub)'
from Contact, Account
where contact.accountid = account.accountid
AND Account.account = 'W.H. Stuart & Associates (Hub)'
|
|
|
|
Re: Execute SQL Problem
Posted: 01 Jun 06 5:46 PM
|
fiogf49gjkf0d I'm actually working at the contact level. What happened is that a user changed the account name at the account level but this doesn't change the name on the hyperlink of the contact detail view. The contact table has a field called "account" that is used to display that name. So, of course, my users can't click on a hyperlink with the old name, so we have to do something!
It's totally stupid but true. |
|
|
|
Re: Execute SQL Problem
Posted: 01 Jun 06 9:35 PM
|
fiogf49gjkf0d There may be other issues here, but try this one:
update sysdba.contact
set account = 'Stuart Securities Corp (Hub)'
where contact.accountid = 'The accountID'
Else you may need to do something like the following. Note: This will update ALL records
update account
set account = AccountSummary.account
from Account, AccountSummary
AND AccountSummary.account <> account.account
update Contact
set account = Account.account
from Contact, Account
AND contact.account <> account.account
|
|
|
|
Re: Execute SQL Problem
Posted: 02 Jun 06 7:51 AM
|
naltrexone online naltrexone low dose fiogf49gjkf0d Oops I forgot to mention a OOTB SLX tool; Integrity Checker. You can run checks against your DB for incorrect data. Add the "Show SQL" checkbox status and test. It will give you a run down of your data. Example of an Account test below.
WGAdmin | Tools | Integrity Checker
>> Bad Account Mirrors (Uppercase) <<
SELECT COUNT(ACCOUNTID) FROM ACCOUNT WHERE ACCOUNT_UC <> UPPER(ACCOUNT) OR (ACCOUNT_UC IS NULL AND ACCOUNT IS NOT NULL)
0 records found
Carla  |
|
|
|
Re: Execute SQL Problem
Posted: 02 Jun 06 11:05 AM
|
coupons for cialis prescription drugs discount cards click here fiogf49gjkf0d Integrity Checker, of course! Actually, I wound up selecting all of the Contact ids, from the contact table that had the old name and then cutting and pasting them into an update statement:
"update contact set account = 'Stuart Securities Corp (Hub)'
where contactid = 'whatevercontactid'"
There were only 44 this time, but I will need a more elegant solution for next time. The statement that you are talking about is at the Account level, I am trying to update the account field at the contact level. I looked in the integrity checker and they are not looking at this field. Do you happen to know what type of SQL syntax that SalesLogix uses in this Execute SQL, I think I have read something about a specific SQL syntax that execute SQL uses but I forget it now. |
|
|
|
Re: Execute SQL Problem
Posted: 02 Jun 06 12:18 PM
|
bentelan a cosa serve bentelan fiale 4 mg fiogf49gjkf0d I got it!
Ok, so maybe I stole it from this post:
SQL 92 from the T-Sql Discussion on SLXDeveloper.com
But it still works!
select
'UPDATE sysdba.contact set account = ''Stuart Securities Corp (Hub)'' where Contactid = ''' + sysdba.contact.Contactid + ''''
FROM sysdba.Contact
WHERE sysdba.contact.account = 'W.H. Stuart & Associates (Hub)'
Thank you for your help Carla! |
|
|
|
Re: Execute SQL Problem
Posted: 06 Jun 06 4:12 PM
|
fiogf49gjkf0d You can select "show sql statements" when you run the Integrity Checker |
|
|
|