Skip to Content
0

merge statement in 16 PL05 changed

Mar 20, 2017 at 05:17 PM

121

avatar image
Former Member

In Sybase 16 PL04 we used

merge ..

when matched ...

then update ...

set @RowsUpdated = @RowsUpdated + 1

This works in PL04 but fails in PL05 with either

Invalid operator for datatype op: BUILTIN FUNCTION type: INT.

or

Incorrect syntax near '@@@V0_INT'.

depending on if the literal autoparam is set or not

Why has this stopped working ?

I can't find anything in the release notes - was this documented ?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Bret Halford
Mar 20, 2017 at 08:34 PM
0

Hi Mike,

Not enough details here to reproduce the issue, but I don't know of any planned changed to the MERGE command. The change in behavior seems likely to be a bug rather than a planned feature.

-bret

Share
10 |10000 characters needed characters left characters exceeded
Avinash Kothare Mar 20, 2017 at 08:50 PM
0

Are you trying to run the set of two statements (i.e. "update ..." followed by the "set" command) as a single block after the "then" key word ?

Looking the syntax of merge, I am not sure if more than two statements are allowed following "then" key word.

In the example from manual I do not see a construct with multiple statements following "then" key word.

May be SAP folks can shed some light if "BEGIN" .... "END" block can be used after "then" for a set of statements.

If so you should be able to use that.

In my opinion this mechanism should be same in SP02 PL04 too !

HTH

Avinash

Below is my test session work, first part when I have two statements following "then"

And second where I have commented out the counter increment essentially single statement following "then" clause.

NOK

1> declare @RowsUpdated integer

2> declare @RowsInserted integer

3> merge into AVI_001 as G

4> using tempdb..mytable as D

5> on D.IDCOL = G.IDCOL

6> when matched 7> then

8> update set G.IDCOL = D.IDCOL, G.DTCOL = D.DTCOL, G.VCCOL = D.VCCOL

9> select @RowsUpdated = @RowsUpdated + 1

10> when not matched

11> then

12> insert(IDCOL, DTCOL, VCCOL)

13> values (D.IDCOL, D.DTCOL, D.VCCOL)

14> select @RowsInserted = @RowsInserted + 1

15> go

Msg 156, Level 15, State 2: Server 'PHI5_SB_16', Line 10: Incorrect syntax near the keyword 'when not matched'.

OK

1> declare @RowsUpdated integer

2> declare @RowsInserted integer

3> merge into AVI_001 as G

4> using tempdb..mytable as D

5> on D.IDCOL = G.IDCOL 6> when matched

7> then

8> update set G.IDCOL = D.IDCOL, G.DTCOL = D.DTCOL, G.VCCOL = D.VCCOL

9> --select @RowsUpdated = @RowsUpdated + 1

10> when not matched

11> then

12> insert(IDCOL, DTCOL, VCCOL) 13> values (D.IDCOL, D.DTCOL, D.VCCOL)

14> --select @RowsInserted = @RowsInserted + 1

15> go

(4 rows affected) 1>

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 21, 2017 at 09:13 AM
0

Bret, What details do you need - here'e the basic statement

merge into db..table as oc

using ( select c.I, c.D, c.S, c.C, c.Value from #tmpe c where c.Value is not NULL ) as t

on t.I = oc.I and t.S = oc.S and t.D = oc.D and t.D = oc.C

when matched then

update set Value = t.Value, ,@RowsUpdated = @RowsUpdated + 1

when not matched then

insert(I, D, S, C, Value) values(t.I, t.D, t.S, t.C, t.Value)

It works in PL04 and not in PL05

Definitely seems like a bug. Looks like another rollback.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Mike,

Ideal would be a complete set of CREATE TABLE ddl scripts, just enough data INSERTS, and the full actual query to be able to reproduce the issue in a newly created database.

Cheers,
-bret

1
avatar image
Former Member Mar 21, 2017 at 10:28 AM
0

Avinash

I don't understand what you're trying to show. Select statements aren't allowed in a merge statement.

See the reply to Bret which shows how to do a merge statement.

Share
10 |10000 characters needed characters left characters exceeded
Avinash Kothare Mar 21, 2017 at 03:00 PM
0

Mike

I (mistakenly) assumed that you are trying to use more than one DML statements for single matched/not matched clause.

Normally a BEGIN ... END construct would help to put a complex block of DML statements where needed.

But merge statement seems to allow only single update or insert statement for each matched/not matched case.

And going by your finding local variables are also not alllowed in SP02PL05.

Avinash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 22, 2017 at 09:25 AM
0

Avinash,

Yes it seems like SAP have introduced a bug in PL05.

Will see what they say.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 28, 2017 at 09:24 AM
0

This bug is getting more weird - changing "allocate max shared memory" to 0 - fixes it on one server but not on another.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 28, 2017 at 02:49 PM
0

We've now found the bug in PL04 so its not just a PL05 issue.

Share
10 |10000 characters needed characters left characters exceeded