Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
SELECT not allowed in script |
Thu, Mar 14 2019 9:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | If I use the code below in a script I get an error - SELECT not allowed.
It works fine if used in EXECUTE IMMEDIATE. UPDATE InProg SET Active = (SELECT COUNT(*) FROM UsersLog X WHERE (X.StartDateTime BETWEEN StartPoint AND EndPoint) OR (X.FinishDateTime BETWEEN StartPoint AND EndPoint) OR (X.StartDateTime < StartPoint AND X.FinishDateTime > EndPoint) ) Roy Lambert |
Thu, Mar 14 2019 11:45 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
In a script or stored procedure, there are 2 different UPDATE statements, one SQL and another PSM. If you include it in an EXECUTE IMMEDIATE it is regular SQL, and it's syntax is the normal syntax of an SQL UPDATE statement, if not enclosed by an EXECUTE statement it's the PSM UPDATE statement for cursor updates - not table updates, and the syntax is: UPDATE <CursorName> SET <ColumnName> = <Value> [, <CursorColumnName> = <Value>] -- Fernando Dias [Team Elevate] |
Thu, Mar 14 2019 11:51 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
If InProg is the name of a cursor, then you are looking for something like this: EXECUTE IMMEDIATE ' SELECT COUNT(*) INTO ? FROM UsersLog X WHERE (X.StartDateTime BETWEEN StartPoint AND EndPoint) OR (X.FinishDateTime BETWEEN StartPoint AND EndPoint) OR (X.StartDateTime < StartPoint AND X.FinishDateTime > EndPoint) ' USING UsersTotal; UPDATE InProg SET 'Active' = UsersTotal; -- Fernando Dias [Team Elevate] |
Fri, Mar 15 2019 3:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
Thanks for that - I haven't tried bunging a result into a variable yet - off to do so. Roy Lambert |
Fri, Mar 15 2019 4:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
Before I try it - what type should UsersTotal be set to? I'm baffled as to how it can update all the affected rows in InProg Roy Lambert |
Fri, Mar 15 2019 12:41 PM | Permanent Link |
Fernando Dias Team Elevate | Roy,
<<Before I try it - what type should UsersTotal be set to? >> DECLARE UsersTotal INTEGER; <<I'm baffled as to how it can update all the affected rows in InProg>> The PSM UPADATE statement updates *only* the current row in InProg cursor. If you want to update all the rows you have to enclose the statement in a WHILE loop. Or, perhaps you don't want to use navigational updates and in that case just use dynamic SQL, with EXECUTE or EXECUTE IMMEDIATE, as appropriate. I'm not sure about what is better in that case unless you give me more details about what you are trying to do. -- Fernando Dias [Team Elevate] |
Sat, Mar 16 2019 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
Thanks, that at least confirms I'm not going mad. Roy Lambert |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |