search in table records using advanced sql query list.

Hi All,

I am using advanced query to list the records. Few filters are there for these records. When used aggregate no issue for search based on (like country seletion, state then city, then district then village ) 5 filters working fine. 

But due to some other functionality purpose i have used sql list. now when select the filter based on like country, state etc. my table records are not effecting. Given proper mapping. 

I have doubt how to give proper therse conditions in sql where clause. 


like the attachment provided the conditon in where clause. 

here i need to fileter based on region, country, manufacture, activity then final proudct family filters.


Thanks in advance. 

 

Solution

Hi

How are you refreshing the data?

If you use a button called "Search", for example, with Submit method, it should work (if your SQL is in the preparation and you are passing the values to the query parameters from local or session variables.

If you use Ajax Submit, you should use a Data Refresh on your SQL (that is located in the Preparation), and then an Ajax Refresh on the Table/List Records in the screen.

If you are using a Local List as the source of the Table/List records, you should use Ajax Submit, and use the Data Refresh to refresh the SQL in the preparation or execute a new query in the screen action, and then assign its list to the Local List.

Cheers

Solution

Thanks Jauch for quick reply. 


I am using search button in screen and using local variable of these region, coutnry etc passing. 

Is the above query where clause is correct? If that correct thenI think,  i have to refresh query and table in search button. lik e this

Gangadhara

Hi Gangadhara,

Did you use the test property of OS's SQL? You should give some inputs and see if the query is working as you desire. If so, you can discard problems in the query itself and focus in refreshing the query and table.

However, I guess i see a ")" misplaced in the last clause (PartName). And also, if you want to select multiple filters at the same time you should work with AND instead of OR. If you don´t give any input to your filter the where clause will get all records since is using wildcards.


Best regards,


João Delgado

I think your problem in the query in the group by and order by section of the last 2 lines. Please check it by removing these 2 lines. Then update these two codes as needed. I hope this will help you to debug it. Waiting for your reply.

Thank you,

Sudip

I would follow the advice given above to possible corrections, mainly to the one João gave about the use of AND instead of the OR, as the % will take care when no value is provided. The OR will cause that ANY condition that match will return values, while the AND will make that only records that match ALL provided values will be returned.

Cheers.

Thanks All, 

Let you know after checking with AND 


BR,

Gangadhara. 

Hi Al, 

Tried with And but no luck. Now also not able to search.

BR
Gangadhara

Gangadhara,


Do you have any error when testing the SQL or it's just returning you an empty list?


Is the "%" working for you? For me, I need to use '%'. Otherwise I have an error.


Without knowing the purpose and the reason for the filters it's harder to offer some help. Now it's about business purpose and what you want to achieve with your constraints.


Best regards,


João Delgado



Hi João Delgado,


used '%' and need these filters to find the particular data from huge. I am having milian of records and i want to filter based on some conditions. Here in sql query i am unable to write these conditions. 


Thanks 

Gangadhara.

Gangadhara,

Again like what the others says, it depends on the business case.

Although I just wanted to nitpick a bit on your OR Field LIKE '%'+parameter+'%'. Isn't it not correct on instances that the value of the parameter is blank?

Regards,

JC