Skip to Content
avatar image
Former Member

merge statement in 16 PL05 changed

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Mar 20, 2017 at 08:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 20, 2017 at 08:50 PM

    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>

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 21, 2017 at 09:13 AM

    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.

    Add comment
    10|10000 characters needed 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

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

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 21, 2017 at 03:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 22, 2017 at 09:25 AM

    Avinash,

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

    Will see what they say.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 28, 2017 at 09:24 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 28, 2017 at 02:49 PM

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

    Add comment
    10|10000 characters needed characters exceeded