Hi all,
I’m having trouble with my advanced SQL query and can’t seem to identify the syntax mistake I made.
I want to automate the updating of invoice statuses in our system and found @Stefan Weber's post very helpful:
How can I update several records using a specific static ID
Has anyone encountered a similar query test error in the past and knows how to fix it? I noticed that the same error occurs in O11 but continues to update the selected records, whereas this does not happen in ODC.
I’ve attached my ODC file for those who have access.
Thanks in advance for your help!
Hi Andre,
Firstly, when I try to open your oml on ODC studio, I get the following error:
Since I can't actually open your file, I have a couple of questions for you:
- is your input 'SelectedInvoice' set as expand inline? It needs to be.
- are you doing your Test SQL with values added to the 'SelectedInvoice' test values? That error is a typical error of when you are trying to test but you don't have data for the input you want to do the IN. You can't do IN with an empty input, it needs to have values.
- are you using the correct syntax in ODC? ODC doesn't run in SQL server, but in Amazon Aurora, which uses PostgreSQL , so if you are using ODC, make sure your syntax is correct.
Hope this helps!
Hello André Smit
Did you put the input parameter "SelectedInvoice" of the query to Expand Inline to "Yes"? If not try change it.If you change the Expand inline to "Yes", you should protect the query against SQL Injection, check this: https://success.outsystems.com/documentation/11/reference/outsystems_apis/sanitization_api/Hope this help.PS: I can't open the .oml, so I wasn't able to check the expand inline property.
Hi @Laura Fidalgo
Thanks for your reply.
To answer your questions:1. Yes, I did.
2. Today I learned something new and very interesting. I was testing with no values, and after a quick debug, I encountered a new error.
So, maybe I am jumping the gun. Should my update only include the attribute that will be changed? In my case, the 'Status' attribute in the Testing_Invoices entity.
I tried that too, but the error is still not very helpful.
3. Also a good question. It seems I might have fixed my problem by just reading the documentation. There appears to be a fault with my UPDATE syntax:
And now it works perfectly!
Thanks for being my soundboard and guiding me through the problem. Now I end the day on a good note!