| |
| Publicado el 29,enero 2016 - 10:44 |
Hi. I love having in query editor the ability of setting null a parameter in order to cut off a piece of condition in WHERE clause.
Ex. WHERE field1 = {p1} AND field2 = {p2}
where setting p1 = Null and p2 = 5 means having only WHERE field2 = 5
What if I need to make parametric an IS NULL condition?
Ex. WHERE (field1 = {p1}) OR (field1 IS NULL)
I need to run this WHERE even cutting off "OR field1 IS NULL" part.
The goal is including or excluding NULL values for Field1 depending on what the user needs. I would use 2 different queries but often the list of fields that need this management could be 10 or more, in the same query...
I did not succeed in making disappear an IS NULL condition by parameters.
Is there a way to do it or should I leave the Query Editor for non-banal ordinary queries and write all these queries by hand?
Thank you.
Stefano |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 10:50 |
PS.
It seems that my question is similar or slightly related to a recent one make by DannHCS.
The topic is the same, I'm not sure if the issue or the needs are the same too.
Maybe should I put my post as extension of that similar topic and delete this one? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 12:03 |
Hi Stefano,
if I understand correct, what I am doing in this case:
creating a string variable and fill it with the query.
using hexecutesql (..., )
so you can manipulate each part with string functions.
eg:
qry is string
condition1 is string
condition2 is string
if (...)
condition1 = "a is NULL"
else
condition1 = ""
if (...)
condition2 = "b > 10"
else
condition2 = ""
qry = "select * from file where " + condition1 + " and " + condition2 ...
So your if's before decide the occurence of the condition in the query.
Hope this helps
Erik |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 14:32 |
Hi,
This would be my approach too.
A little remark on Eriks code: include the "WHERE" in the condition strings, so if you leave them out the sql will still be valid.
Regards,
Piet |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 14:56 |
So parametric conditions including IS NULL implies query by hand...
Then it means that for using Query Editor for complex select queries you need to avoid nullable fields in DB. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 15:13 |
This is easily accomplished in the query editor by creating a calculated item and entering the SQL commands that Erik's suggests.. Nice that calculated items also give you access to WL language and stored procedures, which all can be incorporated in calculating a field.
ie. CASE WHERE wl.DateVaild(Customer.StartDate) THEN 'Yahoo' ELSE 'No way' END |
| |
| |
| | | |
|
| | |