I have a SQL Query which has a parameter with it's Expand Inline property set to yes, containing additional query, when I execute this query with test inputs, it works fine and gathers data, but when I test this using the site I get an Error.
Error: *I'm on breakpoint
This is my query:
This is my input for in SQL Query:
Wilbye Descalsota wrote:
Raphael Ranieri wrote:
Seems like your error is not related to the filter.
It is related with the empty fields in the end of your select '','','' but it is a strange error.
What DataBase are you using SQL, Oracle?
same suspicion, I'm using mariadb
I tried putting numbers ('1','2','3','4') in the '' selects and it works, what would be the best practice for this.
now what I did is the 4 '' selects is named after their structure attribute name. Is this okay?
Hi Wilbye,
Have you tried to replace "" by ' in you SQL Query parameter value?
Just an idea.
Graça
Maria da Graça Peixoto wrote:
Tried this solution, same outcome, works on testing but I still get the error on actual execution
Can you please try blow input parameter :
"{applicant_details}.[full_resume] LIKE '%business%' AND {applicant_details}.[full_resume] LIKE '%analyst%'"
Here I just put single quote for like parameter. I am sure it will work.
But be cautious, setting Expand Inline to yes can lead to security thread so be careful.
Thanks
Pankaj
Pankaj Jain wrote:
Tried this solution, same outcome, it works on testing but I get error on actual execution
Also change the -- comment in your advanced query. This works while testing the query, but won't when it's used in runtime. Outsystems creates an SQL command out of the query with the query in 1 line. In this case, the -- breaks the query. Use /* comment */ instead.
Regards,Lennart
Lennart Kraak wrote:
Hi Lennart,
May be you right, but I added both types of comments in OutSystems11 and it is working fine without error.
ThanksPankaj.
Didn't work for me
Do you really need an expand inline with a dynamic query, or can you use an aggregate for it?
Else, see the remarks above.
Kr Hans
Hans Dollen wrote:
I'll do this as a last resort, I'll look for other solutions first.
Can you execute the query again and go to the executed query table and post a screen shot?
This way we can see what the SQL is trying to execute for real.
To see the tab just click there after executing it as a test:
Cheers and Regards,
RR :)
Here is the screenshot after testing the query:
Oh...Sorry Wilbye, I miss understood, you CAN run it as a test and CAN'T in runtime. Is that right?
I thought it was the opposite.
If this is the case you can show us the log in service center about this error?
Yes, that is correct. Here is the image from the Service Center Error Log:
Ok I see...
As you are using MariDb some rules must be different.
What you should get after executing it using SQL or Oracle would be something like that:
Notice that the entities that were {Entity}.[Attribute] in the SQL tab, changes to [DBName].DBO.[Table].[Column]
However in your case, it uses simple quotes when changing the SQL text, going to 'Table'.'Column'. You can see it in the executed SQL print you posted.
It must be some particularity of MariaDB, and when you use the empty fields in the end of your select, as '' it must understand that it is a table or column,
It is possible to remove then?And try to execute again?
If it is not possible to remove... try to use NULL instead of ''
See if it works
I also tried this, and I got the same error in logs, (Duplicate Column) so I think naming the columns after their attribute is the answer
Yes! Sure!
This actually might work too!
This way the DB will not think it is the same column...
Try it, if it works it is ok to use like this... Will not be a problem! :)
Thank you everyone for the help!
Cool!! :)Happy to help!