Solved

Record having max one empty attribute

  • 23 April 2024
  • 3 replies
  • 47 views
JTH

Hi,

This may sound very easy, but: how do I retrieve records that have code = ‘A’ and more than one empty start dates? So in the end I want to see records 3 & 4 since they have the same id_#, code = ‘A’ and more than 1 empty start date. Would be ideal if this was in ONE Web and not through ONE Desktop.

Person id_# code start_date
1 1 A 1-1-2000
2 1 A 1-2-2000
3 1 A  
4 1 A  
5 2 A 1-1-2000
6 2 A 1-2-2000
7 2 A  

 

select *
from Person
where start_date is empty
and code = ‘A’

group by id_#, code
having count (*) > 1

I tried to use count in advanced expressions, but can't seem to get it to work.

icon

Best answer by ivan.kozlov 24 April 2024, 16:48

View original

3 replies

J
Rank
Userlevel 3
Badge +2

Hi @JTH! To create a SQL Catalog Item to retrieve records with code=’A’ , more than one empty start_dates and null start_date entries (namely records 3 & 4), you can use the following SQL:

SELECT * FROM table_name WHERE id_# IN (SELECT id_# FROM table_name WHERE code = 'A' GROUP BY id_# HAVING COUNT(*) - COUNT(start_date) > 1) AND start_date IS NULL;

JTH

Hi @joyce!

Sorry, if I wasn't clear. It was meant to be through applying a DQ rule on a CI (not SQL).

So basically I have a base table CI as shown above in my first post and I want to be able to get specific records (invalid records that are gathered by the sql query), but I want to be able to do that through DQ Rules applied through ONE Web (not ONE Deskptop). I feel like there should be a way, but I can't seem to get it.

ivan.kozlov
Rank
Userlevel 2
Badge +1

Hi @JTH ,
Not sure if this is exactly what you need but here’s an example of implementation using aggregation rules in ONE:

 

I hope this will be helpful.

Ivan

Reply


Community Terms of Use & ConditionsCookie settings