Parameters in Advanced SQL's LIKE and IN queries

hi,

I want to pass parametes to LIKE and IN queries in my advanced sql.

{Order}.[Name] LIKE CONCAT('%', CONCAT(@Name, '%'))
{Order}.[Number] IN (@Numbers)


These queries do not work while their Expand Inline property is set to "No".


I set the parametes' Expand Inline property to "Yes", then the queries start to work.

But that is while the parameters actually have values such as @Name='Joe' or @Numbers="10, 11".


When they don't have values, they are null values in line. Which leads to sql expression missing and no way for me to NULL check.

CASE @Name WHEN IS NULL THEN ...
this becomes -> CASE WHEN IS NULL THEN...


Is there any way to satisfy both empty and unempty cases with single parameter?

or should I have another parameter with Expand Inline property set to "No" for NULL check?


Thanks in advance

Solution

Hi Sho Katayama,

You may want to make those checks before you pass the inputs to your SQL tool? and build the partial filter/where clause manually. You either have an input parameter equal to 

WHERE {Order}.[Name] LIKE CONCAT('%', CONCAT(@Name, '%'))

or you that input parameter has an empty string, which would mean you wouldn't apply a WHERE clause at all.

Solution

Check if the @Numbers is blank then set it like @Numbers="0" where you are assigning the value of the parameter in sql widget.

Like

As a third posibility, you could put the entire WHERE clause, or part of it, in an Expand Inline Parameter.

Hey Sho,

Are you working on P11 or P10?

In P10 there's a neat function in the sanitize extension to create an integer list or text list (for use in IN statements).

You will still need to catch if the value inside the IN statement is empty though, which you could do by something like

MyEntity.MyField IN (@MyString) OR 
@MyString = ''

Hi Joey,

The latter won't work, as it'll result in invalid SQL (it'll resolve to "IN ()").

Kilian Hekhuis wrote:

Hi Joey,

The latter won't work, as it'll result in invalid SQL (it'll resolve to "IN ()").


Hey Killian? Really, I did not know that, I do know that IN (NULL) or IN ('') are considered valid. Though it will not return any results (since an empty set doesn't match with anything)

Well, if you have the statement "

MyEntity.MyField IN (@MyString) OR @MyString = ''

like that, and @MyString is actually empty (as opposed to containing two single quotes), you'll get a SQL error.

hi everyone and thanks for all your kind replies!


As Jorge mentioned, case where I do not need WHERE clause at all indeed does exist.

I'll follow Jorge and Kilian's advice to put the entire WHERE clause into an Expand Inline parameter.


Let me mark Jorge as solution for this time, but thank you all!


many thanks

Hi Sho,

Glad we could be of help. Happy coding!