Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
6
Erster Beitrag:
vor 2 Jahren, 4 Monaten
Letzter Beitrag:
vor 2 Jahren, 4 Monaten
Beteiligte Autoren:
StefanoG, Davie, Erik Schwarz, Piet van Zanten

Query editor: can we make IS NULL condition parametric?

Startbeitrag von StefanoG am 29.01.2016 09: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

Antworten:

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?

von StefanoG - am 29.01.2016 09:50
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

von Erik Schwarz - am 29.01.2016 11:03
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

von Piet van Zanten - am 29.01.2016 13:32
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.

von StefanoG - am 29.01.2016 13:56
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

von Davie - am 29.01.2016 14:13
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.