Expand Inline Table Name in 8.0.0.24

Expand Inline Table Name in 8.0.0.24

  
Hello, I am trying to get the Table Name to expand inline in an advanced query but it doesn't seem like it is actually expanding. Oracle gives me the error that @tablename does not exist (when I try it hardcoded it works just fine). I've tried everything to get it to work but it doesn't want to expand (using it in instances outside the table name works, which I find strange). Any help would be much appreciated, thanks!
Hi Luka,

If you want to use an entity in an advanced query you must use the proper syntax: {Entity} regardless if you are writing it directly in the SQL syntax or in a expand inline query parameter.
Take into consideration that adding query conditions or joins to an advanced query with an expand inline parameter may lead to performance issues. This is the well know plan cache issue (see slide 9).

Cheers
Thanks for the response, currently I have the advanced query written as
SELECT column1name, column2name 
FROM {@tablename}

with @tablename being an input text parameter and expand inline set to yes
Oracle then tells me that @tablename does not exisit which leads me to believe that it did not expand inline dispite the setting.
You should have:

SELECT column1name, column2name FROM @tablename
where @tablename is an input parameter with expand inline set to yes. This parameter should receive a value such as {Entity}.
But I have serious concerns about this pattern. What is your use case, what are you trying to achieve?
From where are you getting the value for the input parameter of the query. This can be very error prone...
André, I have the same issue with advanced query. Assuming this is a PoC, what could be wrong. I am trying to query the Process table and am using the same pattern that you described above but still get an error 'Process' found in 'expand inline' parameter is an unknown entity.
Hi Pradeep,

I believe you must reference the Process entity first on your module...
André Vieira wrote:
You should have:

SELECT column1name, column2name FROM @tablename
where @tablename is an input parameter with expand inline set to yes. This parameter should receive a value such as {Entity}.
But I have serious concerns about this pattern. What is your use case, what are you trying to achieve?
From where are you getting the value for the input parameter of the query. This can be very error prone...
 I figured this might have been the problem in syntax so I tired many different combinations before asking, what you are suggesting was the second one I tried. None of them worked. Oracle keeps telling me that table @tablename does not exist. @tablename was an input parameter of the query and the expand inline was also set to yes. I also asked someone more familiar then I with outsystems to check my work however he could not find any error in my implimentation (and once again went through several variations of the sql code/input parameters). Interestingly expand inline works perfectly fine if I pass in anything but an entity name.
I'm trying to add some level of dynamicism to my application, I have several dozen entities and want to be able to query them individually depending on the users selection, instead of setting up several dozen queries and a switch statement I want to be able to pass in the enitity name (I get this information from a table that outsystems maintains, showing all the entities a particular espace) into an advanced query and get the two columns of those entities (every entitiy has those two columns in common). This would also help in selecting an entitiy to export into excel.
 
Error in advanced query AdvQuery3 in Preparation in EditPage in MainFlow in ProjectName (SELECT Column1, Column2 FROM @In3): ORA-00942: table or view does not exist... is the exact error I get
 
Hi Luka,

Are your tables local or with database connections?
Also does it work correclly if you write SELECT Column1, Column2 FROM {Process} ?

Regards,
João Rosado
The tables are with database connections (I have a referenced extension, which was made with integration studio)
I've tried it with SELECT Name FROM @In1 where @In1 is "{User}" and it works
Re the issue that I was facing; I found out that the advanced query (the whole query is constructed outside via an extension and passed into the advanced query through an expand inline param) works fine when invoked from the web page using the query. Only the test button appears to have an issue with this and hence I have opened an outsystems support case for this.

Thank you.
Pradeep,

To use the Test button you need to fill in the test values, did you do that? You should have "{Process}" as the value for the @In1 parameter.
Again as you can see this is very error prone, if you pass the wrong value you will get errors in your application. Another thing you must consider is that if you are using the user input for this you must encode that value using the EncodeSql to prevent for SQL injection.

Happy coding!
Cheers.
Thanks Andre. Yes, I have been passing the parameter value in the Test dialog, but it does not work. Any parameter set to 'Expand Inline' in the FROM clause does not work so there appears to be some issue. I am sending a test case to OS support.

Agreed that it is error prone, but used carefully it can be quite powerful..
Hello,

Luka Jurukovksi wrote:
The tables are with database connections (I have a referenced extension, which was made with integration studio)
I've tried it with SELECT Name FROM @In1 where @In1 is "{User}" and it works
In this case, you will need to specify which connection to use, by using the syntax /* %LogicalDatabase%=GetLogicalDatabase({Entity Name}) */ and add this in the beginning of the Adv Query.

Note: Entity Name should be replaced by one Entity name of your extension (.xif).
Test queries won't work, it will only work in runtime.

More info:
http://www.outsystems.com/help/servicestudio/8.0/Using_Data/Advanced_Logic_Canvas.htm

Hope it helps you.

Best regards.
Daniel Martins.