Advanced Sql: To return all rows when the condition is empty

Advanced Sql: To return all rows when the condition is empty

  

Hi All,

I would like to ask regarding advanced query. For example i have the following sql

SELECT * FROM TEST 

WHERE TestName = @name

So if the parameter name is empty, i want the advanced sql to return all the rows in the test table, it is possible??

SELECT * FROM TEST 

WHERE TestName = @name
or @name LIKE ""

Actually... 

SELECT {SomeEntity}.* FROM {SomeEntity} WHERE {SomeEntity}.[SomeAttribute] LIKE '%' + @SomeInputParameter + '%'

is what is commonly used... when SomeInputParameter this will become "%%" which matches all possible values. You just need to be aware that you're searching for the value of SomeInputParameter within the contents of SomeAttribute, not for the whole value of SomeAttribute. If SomeInputParameter is "tes" then it would match any of the following values for SomeAttribute: "testing", "hates", "detested". 


SELECT * FROM TEST 

WHERE decode(@name, " ","all",TestName) = decode(@name, " ","all",@name)

@I Box: that's Oracle only.

Just to add an additional possibility, but not one I'd like to advice in this specific (simple) case, if you use the SQL statement (as opposed to an Aggregate), you can put the entire WHERE clause in an input variable set to Expand Inline, and when calling the SQL decide whether or not to pass a value for thie variable.

That said, @Jace Jace, if that's actually your entire SQL, please use Aggregates! They are far easier to maintain and have all kinds of out-of-the-box advantages. Only resort to SQL as a last resort.

Jorge Martins wrote:

Actually... 

SELECT {SomeEntity}.* FROM {SomeEntity} WHERE {SomeEntity}.[SomeAttribute] LIKE '%' + @SomeInputParameter + '%'

is what is commonly used... when SomeInputParameter this will become "%%" which matches all possible values. You just need to be aware that you're searching for the value of SomeInputParameter within the contents of SomeAttribute, not for the whole value of SomeAttribute. If SomeInputParameter is "tes" then it would match any of the following values for SomeAttribute: "testing", "hates", "detested". 


Hello,

I think it would be nice for the community to have an explanation regarding 'SELECT * FROM {table}' and 'SELECT {table}.* FROM {table}'. I see a lot of people that knows the best practice but doesn't know why :)

About the SQL statement, don't forget that this will prevent the use of a "possible" Index in that attribute. In my opinion, this is not the best option. Only if the user wants to achieve that exactly behavior and brings value to the business. If not, we should prevent it.

Regards,

Miguel



Miguel Sousa wrote:


Hello,

I think it would be nice for the community to have an explanation regarding 'SELECT * FROM {table}' and 'SELECT {table}.* FROM {table}'. I see a lot of people that knows the best practice but doesn't know why :)

Hi Miguel,

What do you think it would be a good idea?
An article? Improving the documentation? 

I see 2 problems in the use of "*". Maybe there are more? 

1. The platform can't check anymore if the output structure matches the SELECT.
2. If the physical table has columns that were deleted in the Entity, those will be returned as well...

Pretty bad in any case :)

Cheers,
Eduardo Jauch

Eduardo

In my modest opinion an article would be a good Idea.


Sorry to give my opinion. I know the question was not for me !!!

 

Hi Miguel,

Miguel Sousa wrote:

I think it would be nice for the community to have an explanation regarding 'SELECT * FROM {table}' and 'SELECT {table}.* FROM {table}'. I see a lot of people that knows the best practice but doesn't know why :)

That's a fair point, I'll try and explain it ;) (although, I'm with Eduardo, maybe a more formal explanation is in order)

The platform doesn't usually perform any destructive changes to the structure database... that means when you change the attributes of an Entity (you delete an existing attribute that's no longer needed, for instance), the underlying database table keeps the old columns, as well as some meta-information about which columns are being used by the current version of the Entity. It does this because you can at any point roll back to a previous version of the module where that Entity had a different structure that still needs to be supported by the database table.

That means when you use just the * in a SQL statement, the platform will pass it as is to the database engine and it will return all table columns there, including old, deleted ones. In order to avoid this you need to use {SomeEntity}.* instead, as this will allow the platform to determine that what we are interested in are all the current attributes of SomeEntity, and the resulting SQL statement will have that replaced by the individual attributes that comprise the current version of the entity attributes (so instead of SELECTing * you will explicitly SELECT something like SomeEntity_TableName.Attribute1, SomeEntity_TableName.Attribute2, SomeEntity_TableName.Attribute7).

About the SQL statement, don't forget that this will prevent the use of a "possible" Index in that attribute. In my opinion, this is not the best option. Only if the user wants to achieve that exactly behavior and brings value to the business. If not, we should prevent it.

Most of the time this is the behaviour people want in a search field, search for partial matches. If you don't want partial matches, the other alternative would be to explicitly test if the search keyword is the empty string like Claring already suggested.


Hi Jorge,

Thank you for the explanation! 

it's good to bring these topics to the community.

Regards

Alberto Ferreira wrote:

Eduardo

In my modest opinion an article would be a good Idea.


Sorry to give my opinion. I know the question was not for me !!!

 

Not a problem, Aberto ;)