Using clausule Merge SQL in outsystems

Hello everyone! 


I'm trying to do my own merge sql using the SQL on outsystems, when I click on button "Test" and when I run it in the application too, this works fine, there are no error. But nothing happens, the record is not actualized in database. Someone foward this problem?


SQL query: 


SyntaxEditor Code Snippet

MERGE INTO {RHPESS_PESSOA} TMP
       USING 
        (select P.[CODIGO_EMPRESA],
       P.[CODIGO],
       P.[NOME],
       P.[DATA_NASCIMENTO],
       P.[CPF],
       P.[CART_TRABALHO],
       P.[IDENTIDADE],
       P.[ORGAO_EXPEDIDOR],
       P.[PIS_PASEP],
       P.[LOGIN_USUARIO],
       P.[DT_ULT_ALTER_USUA],
       P.[NOME_ACESSO]
  from {RHPESS_PESSOA} P 
 where P.[CPF] = @CPF 
    or (P.[NOME] like @NOME and P.[DATA_NASCIMENTO] = @DTNASCIMENTO )) TMP2
           ON ( TMP.[CODIGO] = TMP2.[CODIGO])
           WHEN MATCHED THEN
                  UPDATE SET  
                         TMP.[DT_ULT_ALTER_USUA] = SYSDATE
           WHEN NOT MATCHED THEN INSERT
                    (CODIGO_EMPRESA, CODIGO, NOME, DATA_NASCIMENTO, CPF, CART_TRABALHO, IDENTIDADE, ORGAO_EXPEDIDOR, PIS_PASEP, LOGIN_USUARIO, DT_ULT_ALTER_USUA, NOME_ACESSO )
           VALUES   (@CODIGO_EMPRESA , '0000'||@CPF, @NOME , @DTNASCIMENTO , @CPF , @CART_TRABALHO , @IDENTIDADE , @ORGAO_EXPEDIDOR , @PIS_PASEP , @LOGIN_USUARIO , SYSDATE, @NOME)


It's not working! But there are no erros!  

Couple of things...when posting a DB-related question, it's helpful if you specify which database you're using, so folks don't have to guess. Looking at the syntax, it appears you're targeting Oracle.

My background is more towards SQL Server, and one of the steps I'd use to troubleshoot something like this is to test the query I want to run directly against the DB using SQL Server Management Studio. For Oracle, you would just use whichever equivalent query runner you use/prefer. I'd also consider using a tracing tool to monitor the queries that are being executed against the database. Both of these assume that you have access to the underlying database, of course.

Hi Mikael,

when you are using Advanced Queries and click on Test Button. All SQL here will run in the database in a specific Transaction with a rollback in the end. So you have never seen any results of that unless you run it in runtime.

Best regards

Fabio

The sintax I´m using is similar to oracle and sql server so, maybe, your sql experience can be a little bit doubtful. And the problem it's not a query sintax because there are no erros.  But the database is ORACLE. 

Fábio Fantato wrote:

Hi Mikael,

when you are using Advanced Queries and click on Test Button. All SQL here will run in the database in a specific Transaction with a rollback in the end. So you have never seen any results of that unless you run it in runtime.

Best regards

Fabio

I run it in runtime too. And nothing happens. Thx, man! 


I solved it

SyntaxEditor Code Snippet

 MERGE INTO {RHPESS_PESSOA} TMP
       USING 
        (select @CODIGO_EMPRESA AS [CODIGO_EMPRESA],
                @CODIGO AS [CODIGO],
                @NOME AS [NOME],
                @DTNASCIMENTO AS [DATA_NASCIMENTO],
                @CPF AS [CPF],
                @CART_TRABALHO AS [CART_TRABALHO],
                @IDENTIDADE AS [IDENTIDADE],
                @ORGAO_EXPEDIDOR AS [ORGAO_EXPEDIDOR],
                @PIS_PASEP AS [PIS_PASEP],
                @LOGIN_USUARIO AS [LOGIN_USUARIO],
                SYSDATE AS [DT_ULT_ALTER_USUA],
                @NOME AS [NOME_ACESSO]
  from dual  ) TMP2
           ON ( TMP.[CPF] = TMP2.[CPF] or (TMP.[NOME] = TMP2.[NOME] and TMP.[DATA_NASCIMENTO] = TMP2.[DATA_NASCIMENTO] ))
           WHEN MATCHED THEN
                  UPDATE SET  
                         TMP.[DT_ULT_ALTER_USUA] = SYSDATE
           WHEN NOT MATCHED THEN
 INSERT
                    (CODIGO_EMPRESA, CODIGO, NOME, DATA_NASCIMENTO, CPF, CART_TRABALHO, IDENTIDADE, ORGAO_EXPEDIDOR, PIS_PASEP, LOGIN_USUARIO, DT_ULT_ALTER_USUA, NOME_ACESSO )
           VALUES   (TMP2.[CODIGO_EMPRESA],
       TMP2.[CODIGO],
       TMP2.[NOME],
       TMP2.[DATA_NASCIMENTO],
       TMP2.[CPF],
       TMP2.[CART_TRABALHO],
       TMP2.[IDENTIDADE],
       TMP2.[ORGAO_EXPEDIDOR],
       TMP2.[PIS_PASEP],
       TMP2.[LOGIN_USUARIO],
       TMP2.[DT_ULT_ALTER_USUA],
       TMP2.[NOME_ACESSO])


Oracle

Outsystems 10


Mikael Medeiros wrote:

The sintax I´m using is similar to oracle and sql server so, maybe, your sql experience can be a little bit doubtful. And the problem it's not a query sintax because there are no erros.  But the database is ORACLE. 

No need to be rude if someone is trying to help you


Daniël Kuhlmann wrote:

Mikael Medeiros wrote:

The sintax I´m using is similar to oracle and sql server so, maybe, your sql experience can be a little bit doubtful. And the problem it's not a query sintax because there are no erros.  But the database is ORACLE. 

No need to be rude if someone is trying to help you


Then he could be rude with me? What goes around comes around.


Mikael Medeiros wrote:

Daniël Kuhlmann wrote:

Mikael Medeiros wrote:

The sintax I´m using is similar to oracle and sql server so, maybe, your sql experience can be a little bit doubtful. And the problem it's not a query sintax because there are no erros.  But the database is ORACLE. 

No need to be rude if someone is trying to help you


Then he could be rude with me? What goes around comes around.


Sorry Mikeal,

I do not agree with you. 

First of all if somebody on the forum posts rude comments, you can address that (like I did). There is no need to be rude back.

Second I cannot understand why you got offended from G Andrew Duthie's post.He only gives you suggestions for others to more quickly being able to help you and suggestions to you. Now they can be usefull, or unusefull. His intention was correct.

This forum is for OutSystems proffesional, lets keep the communication also at a professional level.

Regards,

Daniel



Going to chime in briefly, and then close the discussion, as the issue has been solved, and I'm not sure it's productive to continue.

But to be very clear, my goal as a Developer Advocate at OutSystems is to improve things for our developers, and that includes trying my best to help folks here on the forums get answers quickly. My initial comment on that thread was intended to be helpful, not offensive, so I apologize if the way I worded my post seemed critical or negative. That was not my intent.

I appreciate the support, Daniel, and Mikael I hope you'll accept my apology.