选择公式，排除具有空值的列的行 [英] Selection formula excluding rows with columns having null values

问题描述

我有一个奇怪的问题。我有一份报告CR。在选择公式中，我对两个字段进行测试。测试很简单，例如： {field_City} ='巴黎'或{field_Country} ='法国'。

I have a strange issue. I have a report CR. In the Selection Formula I do a test on two fields. The test is simple like that : {field_City} = 'Paris' OR {field_Country} = 'France'.

这是我表中数据的示例：

This is a sample of the data in my table:

|---------------|---------------|---------------|
|   ID_Record   |    Country    |      City     |
|---------------|---------------|---------------|
|        1      |      null     |      Paris    |
|---------------|---------------|---------------|
|        2      |      France   |      null     |
|---------------|---------------|---------------|
|        3      |     France    |      Paris    |
|---------------|---------------|---------------|

选择的结果应为3条记录，但是不包括前两行一列中的空值。然后我更改了选择公式，也考虑了空值：（{field_City} ='Paris'AND（isnull（{field_Country}）OR not（isnull（{field_Country}））））OR（ {field_Country} ='France'AND（isnull（{field_City}）OR not（isnull（{field_City}））））但我仍然只得到最后一条记录！为了确保自己的代码正确，我通过CR显示sql查询中的选项生成了sql查询，然后添加了WHERE子句，在其中写出了与选择公式相同的条件，并且……它给了我3条记录！不幸的是，我无法使用sql查询，我必须找出原因，为什么该公式会排除其中一列中具有空值的记录：(我希望您能为我提供帮助。非常感谢！

The result of the Selection should be the 3 records, however it's excluding the 2 first rows where there is a null value in one of the columns. Then I changed the Selection Formula like this to consider null values too : ({field_City} = 'Paris' AND (isnull({field_Country}) OR not(isnull({field_Country})))) OR ({field_Country} = 'France' AND (isnull({field_City}) OR not(isnull({field_City})))) but I am still getting only the last record ! To ensure myself that my code is correct, I generated the sql query via the option in CR 'Show sql query', then i've added a WHERE clause in which I wrote the same condition that i've put in the Selection Formula, and...... it gave me the 3 records ! Unfortunately I can't work with the sql query, I have to find out why the formula is excluding the records that have a null value in one of the columns :( I hope that you can help me. Thanks a lot !

这是解决方案：（（isnull（{field_Country}）AND {field_City} ='Paris'）OR（isnull（{field_City}）AND { field_Country} ='France'）OR（not（isnull（{field_Country}））AND {field_City} ='Paris'）OR（not（isnull（{field_City}））AND {field_Country} ='France'）），非常感谢Craig！

This is the solution: ((isnull({field_Country}) AND {field_City} = 'Paris') OR (isnull({field_City}) AND {field_Country} = 'France') OR (not(isnull({field_Country})) AND {field_City} = 'Paris') OR (not(isnull({field_City})) AND {field_Country} = 'France')) , Thank you so much Craig!

推荐答案

您需要先测试空值 ：

( Not(Isnull({field_Country})) AND {field_Country}='France' )
OR
( Isnull({field_Country}) AND {field_City}='Paris' )

这篇关于选择公式，排除具有空值的列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！