| UPDATING with INLINE QUERY [message #37312] |
Wed, 30 January 2002 12:30  |
Mohammad Syed
Messages: 18
Registered: November 2000
|
Junior Member |
|
|
I have a procedure with the following update statement
UPDATE sessionLocal_t slt
SET logoffTime = TRUNC(logonTime) + ( SELECT getUTCOffset(UserID)/24
FROM &1..session_t
WHERE ID = HEXTORAW('067077')
)
WHERE TRUNC(logonTime) != TRUNC(sysdate)
AND logoffTime IS NULL ;
The procedure will not compile and gives me the following error code:
PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others <an identifier> ......
Anyone familiar with this type of DML statement?? Thanks in advance!!
|
|
|
|
|
|
| Re: UPDATING with INLINE QUERY (RESOLVED) [message #37331 is a reply to message #37312] |
Thu, 31 January 2002 03:43  |
Mohammad Syed
Messages: 18
Registered: November 2000
|
Junior Member |
|
|
I got it to compile by rewriting it as follows, so I guessthe available options for this kind of DML are:
inline query
expression
field from updating table
field from a table AND expression
Correct usage:
UPDATE &1..sessionLocalTime_t slt
SET logoffTime = ( SELECT TRUNC(logonTime) + ln_ninePM + &1..getUTCOffset_Broker(sessionUserID)/24
FROM &1..session_t
WHERE ID = slt.sessionID
)
WHERE TRUNC(logonTime) != TRUNC(sysdate)
AND logoffTime IS NULL ;
When you use the inline query option, you cannot combine it with an expression and/or field from the updating table. I tried combining it with an expression as follows
UPDATE &1..sessionLocalTime_t slt
SET logoffTime = ( SELECT TRUNC(logonTime) + ln_ninePM + &1..getUTCOffset_Broker(sessionUserID)/24
FROM &1..session_t
WHERE ID = slt.sessionID
) + 1 --> Added expression
WHERE TRUNC(logonTime) != TRUNC(sysdate)
AND logoffTime IS NULL ;
With the query I gave in the previous email, I combined the inline query with fields from the updating table AND an expression. (see red portion of code below)
UPDATE &1..sessionLocalTime_t
SET logoffTime = ( TRUNC(logonTime) + ln_ninePM ) + ( SELECT getUTCOffset_Broker(sessionUserID)/24
FROM session_t
WHERE ID = sessionLocalTime_t.ID )
WHERE TRUNC(logonTime) != TRUNC(sysdate)
AND logoffTime IS NULL ;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
