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

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]

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]

Fernando


Thanks for that - I haven't tried bunging a result into a variable yet - off to do so.

Roy Lambert

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

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]

Fernando


Thanks, that at least confirms I'm not going mad.

Roy Lambert