5/17/2024 8:28:14 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 SQL Server or Oracle database administration related to SalesLogix databases. View the code of conduct for posting guidelines.
|
|
|
|
More SQL Update Help 
Posted: 20 Feb 08 7:22 AM
|
I have another update query that I am trying to run thru SLX Admin, and I'm getting a Fail to Parse error. Here is the code:
UPDATE SYSDBA.U_ACCOUNT
SET ESTCLOSEVAL =
CASE
WHEN (UA.NUM_WEARER > 100)
and (UA.PROGRAM_TYPE in ('No Programmer', 'Direct Sale'))
THEN 300
WHEN (UA.NUM_WEARER > 50 AND UA.NUM_WEARER <= 100)
and (UA.PROGRAM_TYPE in ('No Programmer', 'Direct Sale'))
THEN 250
WHEN (UA.NUM_WEARER >= 30 AND UA.NUM_WEARER <= 50)
and (UA.PROGRAM_TYPE in ('No Programmer', 'Direct Sale'))
THEN 200
WHEN (UA.NUM_WEARER > 500)
and (UA.PROGRAM_TYPE in ('Rental'))
THEN 2000
WHEN (UA.NUM_WEARER >= 300 AND UA.NUM_WEARER <= 500)
and (UA.PROGRAM_TYPE in ('Rental'))
THEN 1000
WHEN (UA.NUM_WEARER > 100 AND UA.NUM_WEARER <= 299)
and (UA.PROGRAM_TYPE in ('Rental'))
THEN 500
WHEN (UA.NUM_WEARER > 50 AND UA.NUM_WEARER <= 100)
and (UA.PROGRAM_TYPE in ('Rental'))
THEN 350
WHEN (UA.NUM_WEARER >= 30 AND UA.NUM_WEARER <= 50)
and (UA.PROGRAM_TYPE in ('Rental'))
THEN 250
ELSE UA.ESTCLOSEVAL
END
from SYSDBA.U_ACCOUNT UA
Does the SLX Admin handle CASE statements? If not, is there another way to write this without breaking it up into several update statements ? We're on SLX 6.1.3. |
|
|
|
Re: More SQL Update Help
Posted: 21 Feb 08 7:24 AM
|
Your problem is:
".. UPDATE... Set ... FROM.."
This syntax will not work with the Provider.
You need to take a sub-select approach:
Update.. Set .. Wehere ... In/= (Select From... where..)
--
RJLedger - rjlSystems |
|
|
|
Re: More SQL Update Help
Posted: 21 Feb 08 8:59 AM
|
Actually, the Update..Set..From does not appear to be the problem. When I removed the CASE statement and added where clauses instead, it worked fine. The code below executed fine in SLX Admin:
UPDATE SYSDBA.U_ACCOUNT
SET MODIFYDATE=GETDATE(),
ESTCLOSEVAL = 1000
FROM SYSDBA.U_ACCOUNT UA
INNER JOIN sysdba.ACCOUNT A ON A.ACCOUNTID = UA.ACCOUNTID
INNER JOIN sysdba.USERINFO UI ON UI.USERID = A.ACCOUNTMANAGERID
WHERE (ESTCLOSEVAL IS NULL OR ESTCLOSEVAL = 0)
AND UI.DIVISION in ('080', '081', '082', '083','086', '087', '090', '095', '096', '097', '098', '217', '218')
AND UA.NUM_WEARER_RANGE = 'A'
AND (UA.NUM_WEARER >= 300 AND UA.NUM_WEARER <= 500)
AND UA.PROGRAM_TYPE in ('Rental')
AND UI.DEPARTMENT = 'SALES' |
|
|
|
Re: More SQL Update Help
Posted: 21 Feb 08 5:08 PM
|
Are you sure that this works? You added it as a query in Tools / Execute SQL and ran it?
I've tried this on a 7.2.1 instance and it does not come close to running - parse error.
UPDATE..FROM is definitely a problem in all of the testing I have done, as is using the sysdba prefix (you are already logged in as sysdba, you should not include the prefix).
Phil |
|
|
|
Re: More SQL Update Help
Posted: 22 Feb 08 2:16 AM
|
Yes, this is 6.1 and didn't have the update../from issue - only vsn 6.2 on have that.
|
|
|
| |
|
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!
|
|
|
|
|
|
|
|