SQL Query - Different Results

SQL Query - Different Results

  

I have a popup with a combobox which is populated through a SQL query.

When i test the query on the platform, i obtained the following results:

However, when i publish and test it on the actual site, the results that show up on the actual dropdown are different from the test results:

How can i get around this?

Hi Sqimi Test,

You shouldn't want to "get around this", you should want to know why that happens! Since I can't see the SQL, it's difficult to tell. Could be translations (if you use them) or Tenant Ids (are you using tenants?).

Also, an SQL for something as simple as retrieving a list for a Combo Box seems like a bad idea - use an Aggregate instead!

Kilian Hekhuis wrote:

Hi Sqimi Test,

You shouldn't want to "get around this", you should want to know why that happens! Since I can't see the SQL, it's difficult to tell. Could be translations (if you use them) or Tenant Ids (are you using tenants?).

Also, an SQL for something as simple as retrieving a list for a Combo Box seems like a bad idea - use an Aggregate instead!

The query is a simple show all complaints identifiers where they don't exist in another query. The objective would be to relate complaints but dont deal with duplicates as they wouldn't show on the combobox.

This is the query  i'm running and there's no tenants.

You kinda ignore what I said, so I'll say it again (and elaborate a bit):

  1. USE AGGREGATES WHEREEVER POSSIBLE. Especially simple queries should never ever ever be written in plain SQL.
  2. Do you use translations? Is there a string literal in the query that could be affected by it?
  3. Is your app multi-tenant (or do you have eSpaces using different User providers)? Of so, is the tenant set using "Configure Tenant to Test Queries" (in the Module menu) the same as the effective tenant when you run the program?

Kilian Hekhuis wrote:

You kinda ignore what I said, so I'll say it again (and elaborate a bit):

  1. USE AGGREGATES WHEREEVER POSSIBLE. Especially simple queries should never ever ever be written in plain SQL.
  2. Do you use translations? Is there a string literal in the query that could be affected by it?
  3. Is your app multi-tenant (or do you have eSpaces using different User providers)? Of so, is the tenant set using "Configure Tenant to Test Queries" (in the Module menu) the same as the effective tenant when you run the program?


i ve added the query in the post above.

Hi Sqimi,


Are you trying to test your query, using the same ComplainIds inside the preview and in runtime? Other check you can do is regarding the data you are expecting. Which them is the right one. This is because outsystems preview will truncate your query to show only a few records in design time. So, could be you are seeing only a part of the results expected. 



Fantato wrote:

Hi Sqimi,


Are you trying to test your query, using the same ComplainIds inside the preview and in runtime? Other check you can do is regarding the data you are expecting. Which them is the right one. This is because outsystems preview will truncate your query to show only a few records in design time. So, could be you are seeing only a part of the results expected. 



Hello Fantato, yes the @complaintid is the same in test and runtime and i got only few records. while in preview i can see that complaintid=1 is related to 2, 3 and 9. So those values dont show up on the testOutputs as expected. but when i go to runtime the combo values are always the same.

Normally i would use an aggregate but this was the way i found to make use of the IN function.

My suspicion is on the output structure, is this structure suitable for the query result? i've tried to make it a list but i get a conversion error.

Hi Sqimi,

Have you tried debugging?... and checking the outcome of the SQL tool at runtime (as well as it's input parameters)?

Can you please post Preparation logic's screenshot? 

And property of that sql in Preparation.

Jorge Martins wrote:

Hi Sqimi,

Have you tried debugging?... and checking the outcome of the SQL tool at runtime (as well as it's input parameters)?

The preparation is just the sql node.

In that breakpoint above i get this runtime values, that don't match the test values from preview.

do i need to assign the values from the SQL output to a temporary list so they can be instantiated?

to feed a combo box it is much easier to use an aggregate. Using SQL to populate a combobox does not seem to be a good idea... I suggest to use an aggregate to feed the combo box. 

Please screenshot of SQL property in Preparation logic. 

I need to see how/what variable passed to SQL. 

Hello SQIMI,

You are looking for complaints in Complaint entity.
Lets take a look at the filter.

1. Only complains with an ID DIFFERENT than the @ComplaintId will be in the list.
2. Also, only complaints whose ID are not in a "list" will be selected. THe list is a list of ComplaintRELATED, that I am assuming is just a ComplaintID related to the same @ComplaintID through the ComplaintID FK.

All good and so on.

With this filter, and without Multi Tenancy, it is impossible to receive different results if @ComplaintID is the same in both the test value and in run time. And with Multi Tenancy, it is virtually impossible to receive similar results like you are receiving. Or you receive exactly the same results, or absolute distinct results.

So, in your new picture, what is the value you used in the TEST tab of the SQL test? It is 1 also, like in Runtime or it is a different value?

Cheers.

Harlin Setiadarma wrote:

Please screenshot of SQL property in Preparation logic. 

I need to see how/what variable passed to SQL. 

Could u be more specific? i'm not understanding but here are all my tabs from the SQL Node in the preview mode.

Harlin is referring to the preparation of the webscreen. He wants to see the properties in the panel of the SQL block. Go to the preparation in the webscreen where you have this SQL block, click once in the sql block, and in the right and bottom part of the platform you will see the properties of the SQL block. 

Eduardo Jauch wrote:

Hello SQIMI,

You are looking for complaints in Complaint entity.
Lets take a look at the filter.

1. Only complains with an ID DIFFERENT than the @ComplaintId will be in the list.
2. Also, only complaints whose ID are not in a "list" will be selected. THe list is a list of ComplaintRELATED, that I am assuming is just a ComplaintID related to the same @ComplaintID through the ComplaintID FK.

All good and so on.

With this filter, and without Multi Tenancy, it is impossible to receive different results if @ComplaintID is the same in both the test value and in run time. And with Multi Tenancy, it is virtually impossible to receive similar results like you are receiving. Or you receive exactly the same results, or absolute distinct results.

So, in your new picture, what is the value you used in the TEST tab of the SQL test? It is 1 also, like in Runtime or it is a different value?

Cheers.

You are correct Eduardo thats what i'm trying to do, you got it right.

I am aware of the correspondence not going both ways and that i should use aggregates. 

But i dont understand the behaviour of what is happening here.this is a screenshot of 2 different id's and the combobox show the same result. The SQL QUERY is in the preparation so the input Id to the Query should be diferent and thus provide different results. And as tested it works in preview..not in runtime.

Jorge Almeida wrote:

Harlin is referring to the preparation of the webscreen. He wants to see the properties in the panel of the SQL block. Go to the preparation in the webscreen where you have this SQL block, click once in the sql block, and in the right and bottom part of the platform you will see the properties of the SQL block. 

AH so this thing.

It comes as an input from a list in another screen and the goes right inside the SQLnode.

Hello SQIMI,

Are you ABSOLUTELY SURE you are using the OUTPUT LIST of the SQL as the source to the RECORD LIST SOURCE of the combo box?

It seems to me that you are using the ENTITY as the source for the combo box, than the different results for the SQL you are using...

Cheers.

Eduardo Jauch wrote:

Hello SQIMI,

Are you ABSOLUTELY SURE you are using the OUTPUT LIST of the SQL as the source to the RECORD LIST SOURCE of the combo box?

It seems to me that you are using the ENTITY as the source for the combo box, than the different results for the SQL you are using...

Cheers.

Weeell not ABSOLUTELY SURE since i got an error. But in the combobox properties i use the SQL as my Source List, does that not suffice to save the new SQL each preparation? 

(it works the same way as aggregates right?, or do i need to save the result to a local list and display the list?)


Well...

I'm running out of ideas... rs
Can you provide a simple oml with this? 

I think to help we need to look into it... (I'm not seeing anything else that could cause this behaviour).

Cheers.

SQIMI TEST wrote:


This is the query  i'm running and there's no tenants.

Some observations:

  1. There's no need for an "AS" after a SELECTed value, it doesn't do anything within the Platform;
  2. I assume "ComplaintRelated" is also an Identifier. In general, it's a good idea to also add "Id" (and in this case, I'd rather name it "RelatedComplaintId" or even "ComplaintRelatedComplaintId", to make clear its actually a "ComplaintId" and not some other Id - but I guess it's too late to change that now);
  3. You are right that Aggregates don't provide for a "NOT IN", so this seems one of the use cases where an SQL is the only option. However, in this specific case you could left join (Aggregate: "With or without") ComplaintRelated with Complaint on ComplaintRelated.ComplaintRelated = Complaint.Id, and then have a Filter that says "ComplaintRelated.ComplaintRelated = NullIdentifier()" (and therefore only selecting Complaint records without a ComplaintRelated record).

Secondly, you say there are no tenants, but do check the "Configure Tenant to Test Queries" and compare it with the "User provider module" of the eSpace. If they are not the same, this could cause the problem.

Can you provide property screenshot of your New Relation button/link? 

I just want to make sure, your input parameter is completely passed on to the Popup screen.

It might be a simple mistakes of passing incorrect input parameter.

From what you tell us, it seems you're passing a static value on ComplaintId input parameter on Popup screen in the New Relation button. 

You could bought me a beer if I'm right... :) 

Because I have ran out of ideas too, if that was not the case. 

Solution

Don't use line comments on Advanced Queries.

Example

    select test.*

    from test

    where test.att1 = 1 -- example comment

instead use

    select test.*

    from test

    where test.att1 = 1 /* example comment */


Cumpz

Update 1: Removed the mention to ORACLE since the problem is not specific to their database. 

Solution

Vitor Mexia wrote:

Don't use line comments on Advanced Queries.

Example

    select test.*

    from test

    where test.att1 = 1 -- example comment

instead use

    select test.*

    from test

    where test.att1 = 1 /* example comment */


Cumpz

Update 1: Removed the mention to ORACLE since the problem is not specific to their database. 

Thank you for the answer Vitor.

This was the problem indeed, the query did work on preview but with the comment lines didn't work on runtime.

 I also found a bug thread reporting this issue. Well now i know what to avoid.

https://www.outsystems.com/forums/discussion/18102/bug-report-sql-commenting-that-screw-up-queries/


Thank you all for your time,

Sqimi

No beer for Harlin though :)