How do you capture empty strings, null values and zero assigned records?

Hi I am trying to write a script that simply will look into a table and count the number of records it has containing null values, empty string values, or zero assigned values.

How do I achieve this in one script?

I started with the below script and ended with this, but I still don't get the results returned correctly, in the sense that blank records are not being returned.
SELECT Count(BUSINESS.ACCT.ACCT_INTRL_ID) FROM BUSINESS.ACCT HAVING (((Count(BUSINESS.ACCT.ACCT_INTRL_ID)) Is Null));
 
SELECT Count(BUSINESS.ACCT.ACCT_INTRL_ID) FROM BUSINESS.ACCT HAVING (((Count(BUSINESS.ACCT.ACCT_INTRL_ID)) =''));

Open in new window

jperez484Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Think it is my syntax is still too much on the MS SQL side -- try this:
SELECT SUM(CASE WHEN NVL(TRIM(BUSINESS.ACCT.ACCT_INTRL_ID),'') = '' OR BUSINESS.ACCT.ACCT_INTRL_ID = '0' THEN 1 ELSE 0 END) AS CountEmptyIntrlID
FROM BUSINESS.ACCT

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
COUNT and other aggregates typically optimize out NULL's.  Think this is what you are looking for.  You will have to see what the datatype of the column is as typically a string will not be 0 but could be '0' so I am assuming that in the code below.
SELECT SUM(CASE WHEN IsNull(BUSINESS.ACCT.ACCT_INTRL_ID,'') IN ('','0') THEN 1 ELSE 0 END) AS CountEmptyIntrlID
FROM BUSINESS.ACCT

Open in new window

0
 
jperez484Author Commented:
I see what your code is trying to do but I get the following error return:


Error starting at line 1 in command:
SELECT SUM(CASE WHEN IsNull(BUSINESS.ACCT.ACCT_INTRL_ID,'') IN ('','0') THEN 1 ELSE 0 END) AS CountEmptyIntrlID
FROM BUSINESS.ACCT
Error at Command Line:1 Column:21
Error report:
SQL Error: ORA-00904: "ISNULL": invalid identifier
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kevin CrossChief Technology OfficerCommented:
Is this Oracle?  My syntax was for MS SQL Server.
0
 
Kevin CrossChief Technology OfficerCommented:
I am not the greatest at Oracle, but NVL should be the equivalent to T-SQL ISNULL:
SELECT SUM(CASE WHEN NVL(BUSINESS.ACCT.ACCT_INTRL_ID,'') IN ('','0') THEN 1 ELSE 0 END) AS CountEmptyIntrlID
FROM BUSINESS.ACCT

Open in new window

0
 
jperez484Author Commented:
I am getting no return for this script, so it must not be capturing the empty -blank records, as I know the data record has empty records in the table.  You mentioned you were handling the 0 as '0' can this be the effect? And yes, Its actually for both environment Oracle SQL developer and Microsoft, sorry for the confusion.


0
 
Kevin CrossChief Technology OfficerCommented:
What is the data type and some sample data of the records you are trying to capture in the count.  It could possibly be ' ' versus empty string or something else.  Please advise.
0
 
jperez484Author Commented:
Great!, this time it worked,  thank you for solving this dilemma for me!
0
 
Kevin CrossChief Technology OfficerCommented:
No problem.

You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.