SQL Formatter (QP5 v5.252.13127.32867) - Problem with Union all Statement

Toad for Oracle & DevOps Toolkit
1

Hello!

When trying to format the SQL below,

MERGE INTO mxsclientpos tgt

 USING (SELECT mxsclientpos_temp.*, 1 AS mask FROM mxsclientpos_temp                  UNION ALL

              SELECT mxsclientpos.*, 0 AS mask

                FROM mxsclientpos, mxsclientpos_temp

               WHERE mxsclientpos_temp.codusuario(+) =

                        mxsclientpos.codusuario

                 AND mxsclientpos_temp.codcli(+) = mxsclientpos.codcli

                 AND mxsclientpos_temp.codusuario IS NULL) src

          ON (tgt.codusuario = src.codusuario AND tgt.codcli = src.codcli)

  WHEN MATCHED

  THEN

     --Atualiza as linhas coincidentes, removendo as linhas que posuem mascara = 0 (linhas existentes na

     --tabela principal que n?o existem na tabela temporaria, recem processada).

     UPDATE SET tgt.dtpositivacao = src.dtpositivacao

     DELETE

             WHERE src.mask = 0

  WHEN NOT MATCHED

  THEN

     --Quando a linha nao for encontrada, simplesmente adiciona-a

     INSERT     VALUES (src.codusuario, src.codcli, src.dtpositivacao);

SQL Formater throws the following error:

[Error] Syntax check (3: 91): ERROR line 3, col 91, ending_line 3, ending_col 95, Found ‘UNION’, Expecting: ) -or- FETCH OFFSET -or- ORDER

The problem appeared after upgrading to TOAD 12.0.0.61. The current formatter options that is in use are attached to this message.

Thanks a lot.
FmtPlus.rar (254 Bytes)

2

Hello,

Your Issue is replicated and there is already a CR #110803 for the issue.

fyi

Thanks

Anand

3

Hello Anand,

Thanks for your response.

So when we could get this bug fixed and how we will know about is resolution?

Thanks!