Hi,

How can I search for 5 random items of an entity in the database?

Regards, 

Jessica. 

Hello Jessica,

You can achieve this using an Advanced SQL Query.


Something like this:

SELECT TOP 5 *
FROM TABLE_NAME
ORDER BY NEWID()


This will return 5 random records from your entity TABLE_NAME.


Let me know if it worked for you.


Kind regards,

Rui Barradas

Hi Jessica,


As per Rui feedback.





Hi Jessica,

Did you manage to resolve your issue?


Kind regards,

Rui Barradas

Rui Barradas wrote:

Hi Jessica,

Did you manage to resolve your issue?


Kind regards,

Rui Barradas

 Hi Rui Barradas, 

Not Yet! (only when you have number 5 itself ...)

I tried to do it in some ways and found the following error when I remove the number five and replace it with a variable (var 1) that contains a form field:

ExamForm.Record.Exam.NumbersMaximumDefault

What am I doing wrong in this case?


 so it was successful!

Regards, 

Jessica Marques. 

(I attached the oml)

Hi Jessica,


MaxRecord DataType - Text

Expand linline-Yes

hope this help



Vijay Malviya wrote:

Hi Jessica,


MaxRecord DataType - Text

Expand linline-Yes

hope this help



 

 Hi, Vijay Malviya

When I perform the test it is working correctly and presenting my questions randomly, but I have an alert from Unexpected SQL, do you know what this message means and what is the best way to work with it?


Thank for the help, 

Best Regards, 

Jessica Marques. 


That's great. 

For Warning check below post:

Message
SELECT statements with 'Expand Inline' parameters cannot be validated against the output structure in <query>
Cause
Since you have a query with expand in line parameters, it is not possible to check whether the Output Structure matches the selected attributes.
Recommendation
You should open your SQL query and check whether the specified Output Structure contains the necessary attributes to fetch the selected attributes, specified in the Inline parameters.


https://success.outsystems.com/Documentation/11/Reference/Errors_and_Warnings/Warnings/Unexpected_SQL_Warning


Vijay M-


Solution

Hello Jessica,

Hope you're doing well.


About your first question:

So basically, in order to use an input variable as a part (or all) of the SQL query itself (like this case), you need to use the Expand Inline property. So what you need to do is simply swap your variable's Data Type to Text and change Expand Inline property to Yes. This will allow you to use your variable on the SELECT statement of the query.


I'm attaching the correct OML so you can see these changes.

Please note that you can only use a parameter with Expand Inline property set to Yes if it is a text.


About your second question:

When you have an input parameter defined as Expand Inline in your SELECT statement of the query, it will cause and automatic warning. This is happening because, since you have a query with Expand Inline parameters, it is not possible for the platform to check whether the Output Structure matches the selected attributes.


You can check more information about these SQL warnings in this documentation.


Anyway, you just need to make sure that your SQL query (with the warning) has the same output Structure as the selected attributes. And we did validate that :)

The warning won't disappear (it is automatically generated by the platform), but you won't have any problem with it. You can just hide it and you're good to go.


Let us know if you have any further questions.


Kind regards,

Rui Barradas

Solution

It works!

the random of the questions on the screen worked when I added these elements to my query.

Thank you for all your help.

Regards, 

Jessica. 

Rui Barradas wrote:

Hello Jessica,

Hope you're doing well.


About your first question:

So basically, in order to use an input variable as a part (or all) of the SQL query itself (like this case), you need to use the Expand Inline property. So what you need to do is simply swap your variable's Data Type to Text and change Expand Inline property to Yes. This will allow you to use your variable on the SELECT statement of the query.


I'm attaching the correct OML so you can see these changes.

Please note that you can only use a parameter with Expand Inline property set to Yes if it is a text.


About your second question:

When you have an input parameter defined as Expand Inline in your SELECT statement of the query, it will cause and automatic warning. This is happening because, since you have a query with Expand Inline parameters, it is not possible for the platform to check whether the Output Structure matches the selected attributes.


You can check more information about these SQL warnings in this documentation.


Anyway, you just need to make sure that your SQL query (with the warning) has the same output Structure as the selected attributes. And we did validate that :)

The warning won't disappear (it is automatically generated by the platform), but you won't have any problem with it. You can just hide it and you're good to go.


Let us know if you have any further questions.


Kind regards,

Rui Barradas

Hi,  

Thank you so much for the explanation.

 Kind regards, 

Jessica Marques. 

You're most welcome Jessica!


Kind regards,

Rui Barradas