Ho to handle "---" in filter of Delete Query?

[Former Member]

Hi guys

once again a question.

I got a MDO having 3 columns A B C.

In there is a dataset looking like ['a', NULL, NULL] what looks like ['a', '---', '---'] in MII.

Want to delete this using a Query.

My Fiterexpression looks like [A] = [Param.1] AND [B] = [Param.2] AND [C] = [Param.3]

Tried to pass "---", "", nothing...but nothing works.

Any idea?

Thanks for your support!

[former_member185280 · Accepted solution]

You may have to use IS NULL for null values.

SQL is null and = null - Stack Overflow

Regards,
Christian

[Former Member · Accepted solution]

Hi Christian, thanks for your reply but I am not sure if I understand how this solves my problem.

Maybe I need to explain a little more.

I got above filter expression in my delete query. I want to remove a dataset if all values match (I do not know what values are key in my scenario...otherwise one could use key values only).

I use that query in some BLS receiving some XML as input. That XML comes from some MDO maintenance GUI showing MDO content.

When I extract selected MDO data from GUI, the resulting input XML that will be sent to BLS will look like this:<Root><A>a</A><B>---</B><C>---</C></Root>.

I now map Input{/Root/A} on Query.Param.1, Input{/Root/B} on Query.Param.2 and so on.

But this of course does not work as --- is a string in this case and MDO has value NULL (?).

Changing my fitler expression to [B] = [Param.1] or [B] IS NULL would end up in a mess as filter may aim on more than one dataset.

A workaround would be to not allow NULL values and set e.g. 0 as default value...but I would like to avoid doing so.

[former_member185280 · Accepted solution]

You could try using the stringif function in the link editor when you assign your params. Something like stringif(<value>=="---", " IS NULL", " = " & <value>  ) may work. I haven't used MDO that much but usually MII just does a replace of your [Param.1] so you typically can pass an '=' in the param etc.

Regards,
Christian

[Former Member · Accepted solution]

Hi, that's a great idea! Thank you Christian