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!
I solved it
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
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
Fábio Fantato wrote:
I run it in runtime too. And nothing happens. Thx, man!
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.
Mikael Medeiros wrote:
No need to be rude if someone is trying to help you
Daniël Kuhlmann wrote:
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.